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) (act
'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
2009/12/1 Laurent Laborde :
> The problem is in the order by, of course.
> If i remove the "order by" the LIMIT 5 is faster (0.044 ms) and do an
> index scan.
> At limit 500 (without order) it still use an index scan and it is
> slightly slower.
> At limit 5000 (without order) it switch to a Bitmap
hummm Adding pgsql-perf :)
On Mon, Nov 30, 2009 at 5:54 PM, Laurent Laborde wrote:
> Friendly greetings !
> I use postgresql 8.3.6.
>
> here is a few info about the table i'm querying :
> -
> - select count(*) from _article : 1730161
Friendly greetings !
I use postgresql 8.3.6.
here is a few info about the table i'm querying :
-
- select count(*) from _article : 17301610
- select count(*) from _article WHERE (_article.bitfield && getbit(0)) : 6729
Here are both requ
