Re: Cost of sort/order by not estimated by the query planner

'morning !

And here is the query plan for :
---------------------------------------
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 5;
Limit  (cost=0.00..2238.33 rows=5 width=1099) (actual
time=17548636.326..17548837.082 rows=5 loops=1)  ->  Index Scan using _article_pkey on _article
(cost=0.00..7762964.53 rows=17341 width=1099) (actual
time=17548636.324..17548837.075 rows=5 loops=1)        Filter: (bitfield && B'1'::bit varying)Total runtime:
17548837.154ms
 


Versus the "limit 500" query plan :
-------------------------------------------
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 500;
Limit  (cost=66229.90..66231.15 rows=500 width=1099) (actual
time=1491.905..1492.146 rows=500 loops=1)  ->  Sort  (cost=66229.90..66273.25 rows=17341 width=1099) (actual
time=1491.904..1492.008 rows=500 loops=1)        Sort Key: id        Sort Method:  top-N heapsort  Memory: 603kB
-> Bitmap Heap Scan on _article  (cost=138.67..65365.82
 
rows=17341 width=1099) (actual time=777.489..1487.120 rows=6729
loops=1)              Recheck Cond: (bitfield && B'1'::bit varying)              ->  Bitmap Index Scan on
idx_article_bitfield
(cost=0.00..134.33 rows=17341 width=0) (actual time=769.799..769.799
rows=6729 loops=1)                    Index Cond: (bitfield && B'1'::bit varying)Total runtime: 1630.690 ms


I will read (and try to understand) all you said yesterday and reply
as soon as i can :)
Thank you !

-- 
Laurent "ker2x" Laborde
Sysadmin & DBA at http://www.over-blog.com/