Re: sequential scan unduly favored over text search gin index

Dne 20.6.2011 18:04, Sushant Sinha napsal(a):
>
> On Mon, 2011-06-20 at 10:58 -0500, Kevin Grittner wrote:
>> Sushant Sinha <sushant354(at)gmail(dot)com> wrote:
>>
>>> I have a tsvector column docvector and a gin index on it
>>> docmeta1_docvector_idx
>>>
>>> I have a simple query "select * from docmeta1 where docvector @@
>>> plainto_tsquery('english', 'free');"
>>>
>>> I find that the planner chooses a sequential scan of the table
>>> even when the index performs orders of magnitude.
>>
>> Did you ANALYZE the table after loading the data and building the
>> index?
> Yes and I mentioned that the row estimates are correct, which indicate
> that the problem is somewhere else.

Hi,

I agree the estimates are damn precise in this case (actually the
estimates are exact). The problem is the planner thinks the seq scan is
about 30% cheaper than the bitmap index scan.

I guess you could poke the planner towards the bitmap scan by lowering
the random_page_cost (the default value is 4, I'd say lowering it to 2
should do the trick).

But be careful, this will influence all the other queries! Those values
should somehow reflect the hardware of your system (type of drives,
amount of RAM, etc.) so you have to test the effects.

regards
Tomas