Re: sequential scan unduly favored over text search gin index

On 2011-06-20 17:38, Sushant Sinha 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. I set random_page_cost = 1.0 for
> the database to favor index use. However, I still see that the cost
> estimate for sequential scan of the entire table (23000) is cheaper than
> the cost of using the index (33000). The time taken for sequential
> access is 5200 ms and for index usage is only 85 ms.
The cost-estimation code for gin-indices are not good in 9.0, this has
hugely been improved in 9.1

http://git.postgresql.org/gitweb?p=postgresql.git&a=search&h=HEAD&st=commit&s=gincost

I think the individual patches apply quite cleanly to 9.0 as far
as I remember.

--
Jesper