Newsgroup: it.comp.software.database
Titolo: PostgreSql e php: script lentissimo

Ho fatto uno script php che mi crea un pdf dalla richiesta ad un db in
PostgreSQL 7.4.8, ed il mio problema è che ci mette un sacco di tempo
per restituire la pagina, molte volte devo fermare io l'esecuzione
bloccando il browser.
Come è possibile ciò?
Ci sono accorgimenti particolari per evitare il rallentamento
dell'elaborazione della pagina in seguito alla richiesta di esecuzione
di query su tabelle con circa 15000 record ?
Thanks

Mentre cercavo di capire la differenza tra una select e un drop database,
xmaxfane ha scritto:

ciao,
prima di tutto devi verificare se la lentezza è 
causata dal db oppure dalla successiva elaborazione
del web server.
Se è vera la prima, posta la query con un explain plan.
15000 record sono nulla se la query ti restituisce anche un
solo record acceduto attraverso un indice.
In caso contrario passo la mano.
bye

 bye
La lentezza è causata dal db, probabilmente perchè per ogni record
della tabella da 15000 va a fare un'altra query su un'altra tabella da
circa 13000 record.
Come faccio a postare tramite  explain plan??!
Thanks

 

 :-|
posta la query e il risultato che ottieni da psql col
comando:
explain LA SELECT CHE ESEGUI PARO PARO A COME LA FA IL PHP;
ciao

Mentre cercavo di capire la differenza tra una select e un drop database,
freCho ha scritto:

ehm, a scanso di equivoci, la faccina sorpresa è perchè
15000 * 13000 diventa un numeretto altino, non perchè non
conosci l'explain plan. Non mi sarei mai permesso.
bye

 circa 13000 record.
Allora, presumendo che la prima query e' del tipo:
SELECT campoA FROM tabella1 WHERE campoB = valore;
E che la seconda query e' del tipo:
SELECT campo1 FROM tabella2 WHERE campo1 = campoA;
probabilmente non usi gli indici. A tal proposito, perche' non fai una
query che mette in join le due tabelle? Nel senso:
SELECT campo1 FROM tabella1, tabella2 WHERE campo1 = campoA
  AND campoB = valore;
Ovviamente anche in questo caso dovresti accertarti di usare gli indici
Enrico

xmaxfane ha scritto:

15000 record sono pochi ma 15000 query sono tante,,,
Prova a riformulare meglio la richiesta come ti hanno già suggerito
con una JOIN.
Giusto per farti capire che postgresql va bene anche con carichi
elevati ti posto un esempio sul mio datawarehouse:
<code>
prod=# \timing
Timing is on.
prod=# SELECT count(seq_doc) FROM vw_tb_nof_fat_eon;
  count
----------
 28347210
(1 row)
Time: 229384.811 ms
prod=# SELECT DISTINCT SEQ_DOC, NUM_POL, PGS_POL, PED_RIF, DAT_EMN FROM
vw_tb_nof_fat_eon WHERE SEQ_DOC=20000000;
 seq_doc  | num_pol | pgs_pol | ped_rif |  dat_emn
----------+---------+---------+---------+------------
 20000000 |   38186 |       1 |  200603 | 2006-03-31
(1 row)
Time: 1.749 ms
</code>
Per farmi la count di circa 28 milioni di record ha impiegato quasi 4
minuti (il server non è proprio una scheggia) però a cercare un
record con indice su chiave, circa 2 millisecondi.

Vi ringrazio per i suggerimenti: da quanto ho capito sono 2 le cose che
devo fare, la prima cercare di migliorare e possibilmente unire le
query tramite un join, la seconda inserire degli indici sui campi che
richiamo maggiormente.
Questi accorgimenti dovrebbero migliorare le performance del mio
database... almeno spero! :-)
Grazie ancora per il vostro aiuto!

... cercare di migliorare e possibilmente unire le query tramite un join...
Scusate ma volevo sapere se tramite un join si possano riunire queste 2
query, in quanto la seconda non è altro che una ripetizione della
prima per ottenere tutti i campi(devo ottenere tutti i campi di tutti i
record con id_anagrafica differente):
$sql_reg = "SELECT id_anagrafica FROM registro WHERE id_cooperativa =
$id_coop AND data_op >= '$data_inizio' AND data_op <= '$data_fine'
GROUP BY id_anagrafica";
$result_reg = pg_query($sql_reg) or die("$sql_reg" . pg_last_error());
while ( $data_reg = pg_fetch_array($result_reg) ) {
    $sql_anag = "SELECT * FROM anagrafica WHERE id_anagrafica =
$data_reg[id_anagrafica]";
    if ( (isset($citta)) && ($citta != "tutto") ) $sql_anag.= " AND
citta_dit = '$citta'";
    if ( (isset($attivita)) && ($attivita != "tutto") ) $sql_anag.="
AND id_attivita = $attivita";
}
Si riesce ad ottenere lo stesso risultato facendo un'unica query e
quindi ciclare la tabella una volta sola invece di come ho fatto io?
Ecco perchè ci mette una vita ad eseguire lo script!!!

Ho modificato le 2 query grazie all'aiuto di 'generosi' riuscendo ad
ottenere un'unica query, ma l'elaborazione rimane comunque troppo
lenta, infatti mi da:
"1507 riga(ghe) - Tempo di esecuzione totale: 75,250.211
ms"(un'infinità!!!)
Di seguito riporto l'explain prodotto da PhpPgAdmin:
QUERY PLAN
Nested Loop  (cost=1.36..7.40 rows=2 width=869)
  ->  Subquery Scan anagrafica_1  (cost=1.36..1.38 rows=1 width=4)
        ->  Unique  (cost=1.36..1.37 rows=1 width=4)
              ->  Sort  (cost=1.36..1.36 rows=1 width=4)
                    Sort Key: id_anagrafica
                    ->  Seq Scan on registro  (cost=0.00..1.35 rows=1
width=4)
                          Filter: ((id_cooperativa = 5) AND (data_op >=
'1900-01-01'::date) AND (data_op <= '2006-12-31'::date))
  ->  Index Scan using pk_anagrafica on anagrafica  (cost=0.00..6.01
rows=1 width=869)
        Index Cond: (anagrafica.id_anagrafica = "outer".id_anagrafica)
Vi ringrazio per i suggerimenti che mi potrete dare!

 '1900-01-01'::date) AND (data_op <= '2006-12-31'::date))
Ad occhio il problema e` qui, dove fa un sequential scan. Dovrei vedere
la query completa, ma se vuoi avere il massimo delle prestazioni
potresti provare a indicizzare il campo data_op.
Bye.
-- 
Alessandro Pellizzari

 xmaxfane wrote:

ciao, 
allora, pur non capendo molto di php tolgo solo l'sql,
e le query dovrebbero essere le seguenti:
SELECT id_anagrafica FROM registro WHERE id_cooperativa = $id_coop
and data_op >= '$data_inizio' AND data_op <= '$data_fine'
GROUP BY id_anagrafica;
SELECT * FROM anagrafica WHERE id_anagrafica = 'tutti i valori ottenuti con
la prima query passati uno per uno';
fare tuning sulla prima significa come minimo indicizzare tutti i campi a cui
accedi con la where condition, ossia id_coop e data_op.
Sul risultato di questa prima query, supponiamo 10 records, esegui
altrettante select con una sola where condition: id_anagrafica.
Quindi devi creare un indice su questo campo per migliorare le
prestazioni.
Però il problema alla fin fine non è solo quì. Premesso che non
conosco postgres benissimo, però se non usi variabili a livello di
sql (bind variables) per ogni query che esegui, il motore del db deve
eseguire _tutti_ gli steps necessari, dal parsing dello statment, al
controllo dei permessi utente ecc. ecc. ecc.
Visto che parliamo di 15000 query  eseguite senza indici e probabilmente
senza riutilizzare la memoria condivisa per l'applicativo...bè..un pochino
di lentezza è il minimo.
Se hai tempo/voglia leggi quà:
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html
ciao

La query è la seguente:
Select anagrafica.* from anagrafica inner join (Select distinct
id_anagrafica from registro WHERE id_cooperativa= 5 AND data_op >=
'01/01/1900' AND data_op <= '31/12/2006') as anagrafica_1 on
anagrafica.id_anagrafica = anagrafica_1.id_anagrafica
La tabella anagrafica ha circa 13000 record, registro 52000 circa,
però filtrata con id_cooperativa=5 solo 3089(infatti ho provato a
sostituire la tabella registro con una vista che appunto contiene 3089
record)
Il campo id_anagrafica di anagrafica è chiave primaria, quindi con
indice, in registro ho messo l'indice a data_op, che è citato nella
condizione della query
Il risultato sono 1507 record
Il tempo di esecuzione purtroppo è in migliaia (75,250.211 ms), quindi
un'eternità!
Possibile che non si possa processare questa quantità di dati?


A questo punto si potrebbe fare:
SELECT * FROM anagrafica INNER JOIN registro USING id_anagrafica WHERE
id_cooperativa = $id_coop AND data_op BETWEEN '$data_inizio' AND
'$data_fine';
Però è da verificare con la struttura referenziale delle tabelle.
M.

xmaxfane ha scritto:

In questo caso da te esplicitato, postgresql non stà usando gli
indici, perché si è creato una vista al volo (anagrafica_1) che non
ha indici, quindi fa una table scan. Perché fai una gruop by su
id_anagrafica?

 id_anagrafica?
Se vi riferite al group by messo sulle query iniziali non contatelo, la
query esatta è l'ultima che ho postato, ossia:


 La query è la seguente:

ciao,
lascio stare per un attimo il db perchè ti hanno già risposto
gli atri. Sposto l'attenzione sul SO. Su che server stai??
Non è che il 90% del tempo il server lo perde swappando su
disco??