Re: planer chooses very bad plan - Mailing list pgsql-performance

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