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

On Wed, Dec 2, 2009 at 11:13 AM, Laurent Laborde <kerdezixe(at)gmail(dot)com> wrote:
>>                                           QUERY PLAN
>> -------------------------------------------------------------------------------------------------
>>  Limit  (cost=0.00..2042.87 rows=5 width=1114)
>>   ->  Index Scan using _article_pkey on _article
>> (cost=0.00..7066684.46 rows=17296 width=1114)
>>         Filter: (bitfield && B'1'::bit varying)
>

Ah, I missed this the first time around. It's scanning _article_pkey
here. Ie, it's scanning the table from the oldest to the newest
article assuming that the values wihch satisfy that constraint are
evenly distributed and it'll find five of them pretty quickly. In
reality there's a correlation between this bit being set and the value
of _article.id and all the ones with it set are towards the end.
Postgres doesn't have any statistics on how multiple columns are
related yet so it can't know this.

If this is an important query you might try having an index on
<bitfield,id> or a partial index on "id where bitfield && B'1' ". The
latter sounds like what you really need

--
greg