From: | "Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com> |
---|---|
To: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Very slow postgreSQL 9.3.4 query |
Date: | 2014-09-26 20:07:11 |
Message-ID: | 3BBE635F64E28D4C899377A61DAA9FE03F09108E@NBSVR-MAIL01.radiantblue.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a cron job that updates the statistics on the "doti_sensor_report" table on the first Saturday of every month. Do you think I should re-generate these statistics more often? This table receives streaming inserts to the volume of about 350 million tuples per-month.
I'll generate new stat's over the weekend, and then execute a new plan
thanks
________________________________
From: Victor Yegorov [vyegorov(at)gmail(dot)com]
Sent: Friday, September 26, 2014 3:15 PM
To: Burgess, Freddie
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query
2014-09-26 19:17 GMT+03:00 Burgess, Freddie <FBurgess(at)radiantblue(dot)com<mailto:FBurgess(at)radiantblue(dot)com>>:
Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
Amount of data processed is also included in the attachment, 185 million row partition.
It looks like your statistics are off:
-> Index Scan using idx_sensor_report_query_y2014m09 on doti_sensor_report_y2014m09 this__1 (cost=0.57..137498.17 rows=3883 width=0) (actual time=168.416..348873.308 rows=443542 loops=1)
Optimizer expects to find ~ 4k rows, while in reality there're 2 orders of magnitude more rows that matches the condition.
Perhaps BitmapIndexScan could be faster here.
--
Victor Y. Yegorov
From | Date | Subject | |
---|---|---|---|
Next Message | Burgess, Freddie | 2014-09-26 22:25:17 | Re: Very slow postgreSQL 9.3.4 query |
Previous Message | Victor Yegorov | 2014-09-26 19:03:17 | Re: after upgrade 8.4->9.3 query is slow not using index scan |