From: Jesper Krogh <jesper@krogh.cc>
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan unduly favored over text search gin index
Date: Mon, 20 Jun 2011 20:58:58 +0200
Message-ID: <4DFF9872.6030104@krogh.cc> (raw)
In-Reply-To: <1308584339.2488.12.camel@dragflick>
References: <1308584339.2488.12.camel@dragflick>
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=gincos...
I think the individual patches apply quite cleanly to 9.0 as far
as I remember.
--
Jesper
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: jesper@krogh.cc
Subject: Re: sequential scan unduly favored over text search gin index
In-Reply-To: <4DFF9872.6030104@krogh.cc>
* 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