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
