From: Jesper Krogh <jesper@krogh.cc>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: darklow <darklow@gmail.com>
Cc: pgsql-performance@postgresql.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: Tue, 10 Jan 2012 22:42:35 +0100
Message-ID: <4F0CB0CB.7090101@krogh.cc> (raw)
In-Reply-To: <25378.1326215074@sss.pgh.pa.us>
References: <CANxtv6XiuiqEkXRJU2vk=xKAFXrLeP7uVhgR-XMCyjgQz29EFQ@mail.gmail.com>
<25378.1326215074@sss.pgh.pa.us>
On 2012-01-10 18:04, Tom Lane wrote:
> darklow<darklow@gmail.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
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, tgl@sss.pgh.pa.us, darklow@gmail.com
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
In-Reply-To: <4F0CB0CB.7090101@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