Re: Query taking long time - Mailing list pgsql-performance

Hello,

New server postgres version is 9.3. I'm not sure if I collected latest statistics after migration, if you mean if the
current_settingsor analyze queries that I posted were collected after migration... yes (notice that there are analyze
querybefore migration and after migration, maybe I didn't illustrate right)  
Sorry for that. Reading the statistics collector manual, I see there are plenty of parameters, and I'm not sure which
oneof them are you interested in, or if there's a query to collect them... 


This is the explain for the query after clearing the cache (name of table has changed, not a mistake...)

explain analyze select * from entity2document  where name='Acetaminophen';
                                                                 QUERY PLAN
              


--------------------------------------------------------------------------------------------------------------------------------------
-------
 Bitmap Heap Scan on entity2document  (cost=104.47..17914.96 rows=4632 width=138) (actual time=62.811..12208.446
rows=2845loops=1) 
   Recheck Cond: ((name)::text = 'Acetaminophen'::text)
   ->  Bitmap Index Scan on entity2document_name_index  (cost=0.00..103.31 rows=4632 width=0) (actual
time=34.357..34.357rows=2845 lo 
ops=1)
         Index Cond: ((name)::text = 'Acetaminophen'::text)
 Total runtime: 12216.115 ms
(5 rows)

It's much better now than with old server (39756.507 ms) however still high. I'd like to improve it...
Thank you very much.

Cheers,

Andrés

PS: Also notice that this is a query after denormalizing the database to avoid joins of very big tables. Once the
performanceis good enough I'd like to normalize it again if it's possible... :-) 






El Mar 20, 2014, a las 12:30 AM, Venkata Balaji Nagothi escribió:

> Sorry, i have not been following this since sometime now.
>
> Hardware configuration is better now. You were running on 8.3.x, can you please help us know what version of Postgres
isthis ? 
>
> Did you collect latest statistics and performed VACUUM after migration ?
>
> Can you get us the EXPLAIN plan for "select * from entity2document2  where name='Acetaminophen' ; " ?
>
> Venkata Balaji N
>
> Sr. Database Administrator
> Fujitsu Australia


**NOTA DE CONFIDENCIALIDAD** Este correo electrónico, y en su caso los ficheros adjuntos, pueden contener información
protegidapara el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier otro tipo de
transmisiónpor parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se ruega
comunicarloal remitente y borrar el mensaje recibido. 
**CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged
informationfor the sole use of the designated recipient named above. Distribution, reproduction or any other use of
thistransmission by any party other than the intended recipient is prohibited. If you are not the intended recipient
pleasecontact the sender and delete all copies.