Re: planer chooses very bad plan

Try random_page_cost=100

- Luke

----- Original Message -----
From: pgsql-performance-owner(at)postgresql(dot)org <pgsql-performance-owner(at)postgresql(dot)org>
To: pgsql-performance(at)postgresql(dot)org <pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance