From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan unduly favored over text search gin index
Date: Mon, 20 Jun 2011 21:01:35 +0200
Message-ID: <4DFF990F.8080906@fuzzy.cz> (raw)
In-Reply-To: <1308585840.2488.13.camel@dragflick>
References: <1308584339.2488.12.camel@dragflick>
<4DFF27C5020000250003E9A4@gw.wicourts.gov>
<1308585840.2488.13.camel@dragflick>
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@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: sequential scan unduly favored over text search gin index
In-Reply-To: <4DFF990F.8080906@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox