Re: Cost of sort/order by not estimated by the query planner - Mailing list pgsql-performance

* 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/