Re: Query taking long time

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_settings or analyze queries that I posted were collected after migration... yes (notice that there are analyze query before 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 one of 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=2845 loops=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.357 rows=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 performance is 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 is this ?
>
> 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 protegida para el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier otro tipo de transmisión por parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se ruega comunicarlo al remitente y borrar el mensaje recibido.
**CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged information for the sole use of the designated recipient named above. Distribution, reproduction or any other use of this transmission by any party other than the intended recipient is prohibited. If you are not the intended recipient please contact the sender and delete all copies.