Try random_page_cost=100 - Luke ----- Original Message ----- From: pgsql-performance-owner@xxxxxxxxxxxxxx <pgsql-performance-owner@xxxxxxxxxxxxxx> To: pgsql-performance@xxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxx> Sent: Sun Apr 11 14:12:30 2010 Subject: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
