Skip site navigation (1) Skip section navigation (2)

Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: darklow <darklow(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified
Date: 2012-01-10 21:42:35
Message-ID: 4F0CB0CB.7090101@krogh.cc (view raw or flat)
Thread:
Lists: pgsql-performance
On 2012-01-10 18:04, Tom Lane wrote:
> darklow<darklow(at)gmail(dot)com>  writes:
>> But the performance problems starts when i do the same query specifying
>> LIMIT.
>> *SELECT id FROM cache WHERE tsv @@ to_tsquery('test:*') limit 20;*
>> By some reason index is not used.
> It apparently thinks there are enough matches that it might as well just
> seqscan the table and expect to find some matches at random, in less
> time than using the index would take.
>
> The estimate seems to be off quite a bit, so maybe raising the stats
> target for this column would help.
The cost of matching ts_match_vq against a toasted column
is not calculated correctly. This is completely parallel with
http://archives.postgresql.org/pgsql-hackers/2011-11/msg01754.php

Try raising the cost for ts_match_vq(tsvector,tsquery) that help a bit, but
its hard to get the cost high enough.

Raising statistics target  helps too..

-- 
Jesper

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2012-01-11 00:19:00
Subject: Re: Subquery flattening causing sequential scan
Previous:From: Robert HaasDate: 2012-01-10 21:21:26
Subject: Re: pg_upgrade failure "contrib" issue?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group