DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=dbxYInlhQCuW+iFc4+zr43nvFXlWv7/FoCRAAeM0duc3uStFnxSOMTF2iQLEHyTtzv
	EvzoDz6MZw2vytTUf+LHDLXCEJuV0mVw++GLomAjJFRpSkyVhh+XtvIyynk/u+jlmRtd
	1lUSSx6Tpm1ufse3kP+EUB3CT8R5iVLQyfNq0=
Message-ID: <4BC23B3E.5020500@gmail.com>
Date: Sun, 11 Apr 2010 23:12:30 +0200
From: Corin <wakathane@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: planer chooses very bad plan
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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