Question about (probably wrong) index scan cost for conditional indexes

I not sure it is bug or just planner work that way.
Postgresql 9.1.2 on Linux.

But it seems that index scan cost for very narrow/selective conditional
indexes is greatly overestimated at least in some cases.

In my case I have an special conditional index like:

"news_dtime_in208section_active_key2" btree (dtime) WHERE status = 1 AND
class::text = 'Sports::News'::text AND sections && '{208}'::integer[]

And query:

db=# EXPLAIN ANALYZE select * from news where (status = 1) and (class =
'Sports::News') and (sections && '{208}') order by dtime limit 10;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..26.38 rows=10 width=1262) (actual time=0.040..0.082
rows=10 loops=1)
-> Index Scan using news_dtime_in208section_active_key2 on news
(cost=0.00..1429.55 rows=542 width=1262) (actual time=0.038..0.073 rows=10
loops=1)
Total runtime: 0.142 ms
(3 rows)

I see no reasons why cost of that query that high... i think it should be
very close equvalent in cost of query:

"news_pkey" PRIMARY KEY, btree (id)

db=# EXPLAIN ANALYZE select * from news order by id limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.33 rows=10 width=1262) (actual time=0.043..0.085
rows=10 loops=1)
-> Index Scan using news_pkey on news (cost=0.00..25944.34 rows=775090
width=1262) (actual time=0.041..0.077 rows=10 loops=1)
Total runtime: 0.147 ms
(3 rows)

(and if you compare real execution times - they are same but cost is
different by 2 orders).
No changes of costing setting have an effect that difference.

That problem leads to switching to very slow plan for medium limits:

db=# EXPLAIN ANALYZE select * from news where (status = 1) and (class =
'Sports::News') and (sections && '{208}') order by dtime limit 40;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=91.97..92.07 rows=40 width=1262) (actual
time=630.865..630.889 rows=40 loops=1)
-> Sort (cost=91.97..93.32 rows=542 width=1262) (actual
time=630.862..630.872 rows=40 loops=1)
Sort Key: dtime
Sort Method: top-N heapsort Memory: 89kB
-> Bitmap Heap Scan on news (cost=6.18..74.83 rows=542
width=1262) (actual time=145.816..412.254 rows=262432 loops=1)
Recheck Cond: ((sections && '{208}'::integer[]) AND (status
= 1) AND ((class)::text = 'Sports::News'::text))
-> Bitmap Index Scan on news_sections_gin2_special
(cost=0.00..6.05 rows=542 width=0) (actual time=98.954..98.954 rows=262754
loops=1)
Index Cond: (sections && '{208}'::integer[])
Total runtime: 632.049 ms
(9 rows)

Kind regards,
Maksym

--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
"If they can send one man to the moon... why can't they send them all?"

МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."