Re: Query taking long time
От | acanada |
---|---|
Тема | Re: Query taking long time |
Дата | |
Msg-id | FF41550A-F19F-4466-859A-B24CB75F4734@cnio.es обсуждение исходный текст |
Ответ на | Re: Query taking long time (Venkata Balaji Nagothi <vbnpgc@gmail.com>) |
Ответы |
Re: Query taking long time
(Venkata Balaji Nagothi <vbnpgc@gmail.com>)
|
Список | 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.
В списке pgsql-performance по дате отправления: