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

* without order by, limit 5 : 70ms
----------------------------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
LIMIT 5;

QUERY PLAN :
Limit (cost=0.00..20.03 rows=5 width=1109) (actual
time=70.190..70.265 rows=5 loops=1)
-> Index Scan using idx_article_bitfield on _article
(cost=0.00..69290.99 rows=17298 width=1109) (actual
time=70.188..70.260 rows=5 loops=1)
Index Cond: (bitfield && B'1'::bit varying)
Total runtime: 70.406 ms
(4 rows)

* without order by, limit 500 (same plan as above) : 371ms
------------------------------------------------------------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
LIMIT 500;

QUERY PLAN:
Limit (cost=0.00..2002.86 rows=500 width=1109) (actual
time=0.087..371.257 rows=500 loops=1)
-> Index Scan using idx_article_bitfield on _article
(cost=0.00..69290.99 rows=17298 width=1109) (actual
time=0.086..371.075 rows=500 loops=1)
Index Cond: (bitfield && B'1'::bit varying)
Total runtime: 371.369 ms

* without order by, limit 5000 (query plan changed) : 1307ms
-------------------------------------------------------------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
LIMIT 5000;

QUERY PLAN :
Limit (cost=138.34..18971.86 rows=5000 width=1109) (actual
time=53.782..1307.173 rows=5000 loops=1)
-> Bitmap Heap Scan on _article (cost=138.34..65294.79 rows=17298
width=1109) (actual time=53.781..1305.565 rows=5000 loops=1)
Recheck Cond: (bitfield && B'1'::bit varying)
-> Bitmap Index Scan on idx_article_bitfield
(cost=0.00..134.01 rows=17298 width=0) (actual time=53.606..53.606
rows=6743 loops=1)
Index Cond: (bitfield && B'1'::bit varying)
Total runtime: 1307.972 ms

So... *without* "order by", differents limit and different query plan
: the queries are fast.

* with order by, limit 5 :
------------------------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 5;

QUERY PLAN :
Mmmm.... the query is running since 2h ... waiting, waiting.

* with order by, limit 500 : 546ms
-------------------------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 500;
QUERY PLAN :
Limit (cost=66156.73..66157.98 rows=500 width=1109) (actual
time=545.671..545.900 rows=500 loops=1)
-> Sort (cost=66156.73..66199.98 rows=17298 width=1109) (actual
time=545.670..545.766 rows=500 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 603kB
-> Bitmap Heap Scan on _article (cost=138.34..65294.79
rows=17298 width=1109) (actual time=1.059..541.359 rows=6729 loops=1)
Recheck Cond: (bitfield && B'1'::bit varying)
-> Bitmap Index Scan on idx_article_bitfield
(cost=0.00..134.01 rows=17298 width=0) (actual time=0.922..0.922
rows=6743 loops=1)
Index Cond: (bitfield && B'1'::bit varying)
Total runtime: 546.163 ms

Now... with ordery by, different limit, different query plan, the
limit 5 query is insanly *SLOW* (while the limit 500 is super fast).

What is think : The query planner do not consider the time taken by
the order by... which is *much* slower !!

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