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.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
