Message-ID: <4DFF9872.6030104@krogh.cc>
Date: Mon, 20 Jun 2011 20:58:58 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan unduly favored over text search gin
 index
References: <1308584339.2488.12.camel@dragflick>
In-Reply-To: <1308584339.2488.12.camel@dragflick>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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