From: | Laurent Laborde <kerdezixe(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Greg Stark <gsstark(at)mit(dot)edu>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Cost of sort/order by not estimated by the query planner |
Date: | 2009-12-02 15:32:44 |
Message-ID: | 8a1bfe660912020732s251f6eddr49036b138781cb49@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-12-02 15:48:17 | Re: Page-level version upgrade (was: Block-level CRC checks) |
Previous Message | Tom Lane | 2009-12-02 15:19:33 | Re: Application name patch - v4 |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-12-02 16:47:44 | Re: Cost of sort/order by not estimated by the query planner |
Previous Message | Craig Ringer | 2009-12-02 13:46:50 | Re: Order by (for 15 rows) adds 30 seconds to query time |