planer chooses very bad plan
От | Corin |
---|---|
Тема | planer chooses very bad plan |
Дата | |
Msg-id | 4BC23B3E.5020500@gmail.com обсуждение исходный текст |
Ответы |
Re: planer chooses very bad plan
(Scott Marlowe <scott.marlowe@gmail.com>)
Re: planer chooses very bad plan ("Pierre C" <lists@peufeu.com>) Re: planer chooses very bad plan (Hannu Krosing <hannu@2ndquadrant.com>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Scott MarloweДата:
Сообщение: Re: [PERFORM] About “context-switching issue on Xeon” test case ?