Thread: planer chooses very bad plan
Hi, I'm having a query where the planer chooses a very bad plan. explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) ORDER BY id DESC LIMIT 10 OFFSET 0 "Limit (cost=0.00..1557.67 rows=10 width=78) (actual time=0.096..2750.058 rows=5 loops=1)" " -> Index Scan Backward using telegrams_pkey on telegrams (cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052 rows=5 loops=1)" " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))" "Total runtime: 2750.124 ms" When I force the planer not use do index scans, the plans looks MUCH better (10.000x faster): set enable_indexscan = false; explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) ORDER BY id DESC LIMIT 10 OFFSET 0 "Limit (cost=2547.16..2547.16 rows=10 width=78) (actual time=0.179..0.185 rows=5 loops=1)" " -> Sort (cost=2547.16..2547.41 rows=1005 width=78) (actual time=0.177..0.178 rows=5 loops=1)" " Sort Key: id" " Sort Method: quicksort Memory: 26kB" " -> Bitmap Heap Scan on telegrams (cost=17.39..2544.98 rows=1005 width=78) (actual time=0.124..0.158 rows=5 loops=1)" " Recheck Cond: ((recipient_id = 508933) OR (user_id = 508933))" " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))" " -> BitmapOr (cost=17.39..17.39 rows=1085 width=0) (actual time=0.104..0.104 rows=0 loops=1)" " -> Bitmap Index Scan on telegrams_recipient (cost=0.00..8.67 rows=536 width=0) (actual time=0.033..0.033 rows=1 loops=1)" " Index Cond: (recipient_id = 508933)" " -> Bitmap Index Scan on telegrams_user (cost=0.00..8.67 rows=549 width=0) (actual time=0.069..0.069 rows=4 loops=1)" " Index Cond: (user_id = 508933)" "Total runtime: 0.276 ms" The table contains several millions records and it's just be reindexed/analyzed. Are there any parameters I can tune so that pgsql itself chooses the best plan? :) # - Memory - shared_buffers = 256MB temp_buffers = 32MB work_mem = 4MB maintenance_work_mem = 32MB # - Planner Cost Constants - seq_page_cost = 1.0 random_page_cost = 2.5 cpu_tuple_cost = 0.001 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.00025 effective_cache_size = 20GB # - Genetic Query Optimizer - geqo = on Thanks, Corin
On Sun, Apr 11, 2010 at 3:12 PM, Corin <wakathane@gmail.com> wrote: > Hi, > > I'm having a query where the planer chooses a very bad plan. In both instances your number of rows estimated is WAY higher than the actual number of rows returned. Perhaps if you increased default_statistics_target to 100, 200, 500 etc. re-analyzed, and then reun explain analyze again. Also increasing work_mem might encourage the bitmap index scans to occur.
Try random_page_cost=100 - Luke ----- Original Message ----- From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org> To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org> Sent: Sun Apr 11 14:12:30 2010 Subject: [PERFORM] planer chooses very bad plan Hi, I'm having a query where the planer chooses a very bad plan. explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) ORDER BY id DESC LIMIT 10 OFFSET 0 "Limit (cost=0.00..1557.67 rows=10 width=78) (actual time=0.096..2750.058 rows=5 loops=1)" " -> Index Scan Backward using telegrams_pkey on telegrams (cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052 rows=5 loops=1)" " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))" "Total runtime: 2750.124 ms" When I force the planer not use do index scans, the plans looks MUCH better (10.000x faster): set enable_indexscan = false; explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) ORDER BY id DESC LIMIT 10 OFFSET 0 "Limit (cost=2547.16..2547.16 rows=10 width=78) (actual time=0.179..0.185 rows=5 loops=1)" " -> Sort (cost=2547.16..2547.41 rows=1005 width=78) (actual time=0.177..0.178 rows=5 loops=1)" " Sort Key: id" " Sort Method: quicksort Memory: 26kB" " -> Bitmap Heap Scan on telegrams (cost=17.39..2544.98 rows=1005 width=78) (actual time=0.124..0.158 rows=5 loops=1)" " Recheck Cond: ((recipient_id = 508933) OR (user_id = 508933))" " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) OR ((user_id = 508933) AND (NOT user_deleted)))" " -> BitmapOr (cost=17.39..17.39 rows=1085 width=0) (actual time=0.104..0.104 rows=0 loops=1)" " -> Bitmap Index Scan on telegrams_recipient (cost=0.00..8.67 rows=536 width=0) (actual time=0.033..0.033 rows=1 loops=1)" " Index Cond: (recipient_id = 508933)" " -> Bitmap Index Scan on telegrams_user (cost=0.00..8.67 rows=549 width=0) (actual time=0.069..0.069 rows=4 loops=1)" " Index Cond: (user_id = 508933)" "Total runtime: 0.276 ms" The table contains several millions records and it's just be reindexed/analyzed. Are there any parameters I can tune so that pgsql itself chooses the best plan? :) # - Memory - shared_buffers = 256MB temp_buffers = 32MB work_mem = 4MB maintenance_work_mem = 32MB # - Planner Cost Constants - seq_page_cost = 1.0 random_page_cost = 2.5 cpu_tuple_cost = 0.001 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.00025 effective_cache_size = 20GB # - Genetic Query Optimizer - geqo = on Thanks, Corin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On 11.04.2010 23:18, Scott Marlowe wrote: > In both instances your number of rows estimated is WAY higher than the > actual number of rows returned. Perhaps if you increased > default_statistics_target to 100, 200, 500 etc. re-analyzed, and then > reun explain analyze again. > > Also increasing work_mem might encourage the bitmap index scans to occur. > Increasing the statistics >= 500 indeed helped a lot and causes the planner to choose a good plan. :) I'm now thinking about increasing the default_statistics_target of the whole server from the default (100) to 1000, because I have many tables with similar data. As the size of the table index seems not change at all, I wonder how much additional storage is needed? I only care about runtime performance: are inserts/updates affected by this change? Or is only analyze affected (only run once during the night)? Thanks, Corin
On 11.04.2010 23:22, Luke Lonergan wrote: > Try random_page_cost=100 > Increasing random_page_const to 100 (it was 2.5 before) did not help, but lowering it <=1.5 helped. As almost the whole dataset fit's into memory, I think I'll change it permanently to 1.5 (seq_page is 1.0). I'll also increase the default_statistics to 1000, because this also seems to help a lot. Thanks, Corin
On Sun, Apr 11, 2010 at 4:41 PM, Corin <wakathane@gmail.com> wrote: > On 11.04.2010 23:18, Scott Marlowe wrote: >> >> In both instances your number of rows estimated is WAY higher than the >> actual number of rows returned. Perhaps if you increased >> default_statistics_target to 100, 200, 500 etc. re-analyzed, and then >> reun explain analyze again. >> >> Also increasing work_mem might encourage the bitmap index scans to occur. >> > > Increasing the statistics >= 500 indeed helped a lot and causes the planner > to choose a good plan. :) > > I'm now thinking about increasing the default_statistics_target of the whole > server from the default (100) to 1000, because I have many tables with > similar data. As the size of the table index seems not change at all, I > wonder how much additional storage is needed? I only care about runtime > performance: are inserts/updates affected by this change? Or is only analyze > affected (only run once during the night)? default stats target has more to do with how many distinct values / ranges of values you have. If your data has a nice smooth curve of distribution smaller values are ok. Large datasets with very weird data distributions can throw off the planner. There's a cost for both analyzing and for query planning. If 500 fixes this table, and all the other tables are fine at 100 then it might be worth doing an alter table alter column for just this column. However, then you've got to worry about time spent monitoring and analyzing queries in the database for if / when they need a higher stats target. Also, look at increasing effective cache size if the db fits into memory. Lowering random page cost helps too.
> explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 > AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) > ORDER BY id DESC LIMIT 10 OFFSET 0 If you need very fast performance on this query, you need to be able to use the index for ordering. Note that the following query will only optimize the first page of results in the case you want to display BOTH sent and received telegrams. - Create an index on (recipient_id, id) WHERE NOT recipient_deleted - Create an index on (user_id, id) WHERE NOT user_deleted - Drop redundant indexes (recipient_id) and (user_id) SELECT * FROM ( SELECT * FROM "telegrams" WHERE recipient_id=508933 AND recipient_deleted=FALSE ORDER BY id DESC LIMIT 10 UNION ALL SELECT * FROM "telegrams" WHERE user_id=508933 AND user_deleted=FALSE ORDER BY id DESC LIMIT 10 ) AS foo ORDER BY id DESC LIMIT 10; These indexes will also optimize the queries where you only display the inbox and outbox, in which case it will be able to use the index for ordering on any page, because there will be no UNION.
On Sun, 2010-04-11 at 23:12 +0200, Corin wrote: > Hi, > > I'm having a query where the planer chooses a very bad plan. > > explain analyze SELECT * FROM "telegrams" WHERE ((recipient_id=508933 > AND recipient_deleted=FALSE) OR (user_id=508933 AND user_deleted=FALSE)) > ORDER BY id DESC LIMIT 10 OFFSET 0 > > "Limit (cost=0.00..1557.67 rows=10 width=78) (actual > time=0.096..2750.058 rows=5 loops=1)" > " -> Index Scan Backward using telegrams_pkey on telegrams > (cost=0.00..156545.47 rows=1005 width=78) (actual time=0.093..2750.052 > rows=5 loops=1)" > " Filter: (((recipient_id = 508933) AND (NOT recipient_deleted)) > OR ((user_id = 508933) AND (NOT user_deleted)))" > "Total runtime: 2750.124 ms" You could check if creating special deleted_x indexes helps do CREATE INDEX tgrm_deleted_recipent_index ON telegrams(recipient_id) WHERE recipient_deleted=FALSE; CREATE INDEX tgrm_deleted_user_index ON telegrams(user_id) WHERE user_deleted=FALSE; (if on live system, use "CREATE INDEX CONCURRENTLY ...") -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training