From: | Laurent Laborde <kerdezixe(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Cost of sort/order by not estimated by the query planner |
Date: | 2009-12-03 09:08:06 |
Message-ID: | 8a1bfe660912030108o5fae7174tf8e59a84e25f9636@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
The table is clustered by by blog_id.
So, for testing purpose, i tried an ORDER BY blog_id.
limit 500 :
-------------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.blog_id ASC
LIMIT 500;
Limit (cost=66229.90..66231.15 rows=500 width=1099) (actual
time=9.368..9.580 rows=500 loops=1)
-> Sort (cost=66229.90..66273.25 rows=17341 width=1099) (actual
time=9.367..9.443 rows=500 loops=1)
Sort Key: blog_id
Sort Method: top-N heapsort Memory: 660kB
-> Bitmap Heap Scan on _article (cost=138.67..65365.82
rows=17341 width=1099) (actual time=0.905..4.042 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=0.772..0.772
rows=6729 loops=1)
Index Cond: (bitfield && B'1'::bit varying)
Total runtime: 9.824 ms
Limit 5 :
----------
explain analyze SELECT *
FROM _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.blog_id ASC
LIMIT 5;
Limit (cost=0.00..1419.22 rows=5 width=1099) (actual
time=125076.420..280419.143 rows=5 loops=1)
-> Index Scan using idx_article_blog_id on _article
(cost=0.00..4922126.37 rows=17341 width=1099) (actual
time=125076.419..280419.137 rows=5 loops=1)
Filter: (bitfield && B'1'::bit varying)
Total runtime: 280419.241 ms
--
Laurent "ker2x" Laborde
Sysadmin & DBA at http://www.over-blog.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Avi Rozen | 2009-12-03 10:35:29 | Re: [Bacula-users] Catastrophic changes to PostgreSQL 8.4 |
Previous Message | Laurent Laborde | 2009-12-03 08:51:25 | Re: Cost of sort/order by not estimated by the query planner |
From | Date | Subject | |
---|---|---|---|
Next Message | Laurent Laborde | 2009-12-03 09:44:28 | Re: Analyse without locking? |
Previous Message | Craig Ringer | 2009-12-03 08:52:16 | Re: [BUGS] BUG #5228: Execution of prepared query is slow when timestamp parameter is used |