Message-ID: <5357B95B.2050008@vmware.com>
Date: Wed, 23 Apr 2014 16:00:11 +0300
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Icedove/24.4.0
MIME-Version: 1.0
To: Ivan Voras <ivoras@freebsd.org>
CC: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: tsearch2, large data and indexes
References: 
 <CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com>
 <53560EE5.3080300@vmware.com>
 <CAF-QHFWbee3Anp+KKc5DEe2wufHtWYZ8zc1ysqXu2-0SW+v-UA@mail.gmail.com>
In-Reply-To: 
 <CAF-QHFWbee3Anp+KKc5DEe2wufHtWYZ8zc1ysqXu2-0SW+v-UA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 04/22/2014 10:57 AM, Ivan Voras wrote:
> On 22 April 2014 08:40, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
>> On 04/20/2014 02:15 AM, Ivan Voras wrote:
>>> More details: after thinking about it some more, it might have
>>> something to do with tsearch2 and indexes: the large data in this case
>>> is a tsvector, indexed with GIN, and the query plan involves a
>>> re-check condition.
>>>
>>> The query is of the form:
>>> SELECT simple_fields FROM table WHERE fts @@ to_tsquery('...').
>>>
>>> Does the "re-check condition" mean that the original tsvector data is
>>> always read from the table in addition to the index?
>>
>> Yes, if the re-check condition involves the fts column. I don't see why you
>> would have a re-check condition with a query like that, though. Are there
>> some other WHERE-conditions that you didn't show us?
>
> Yes, I've read about tsearch2 and GIN indexes and there shouldn't be a
> recheck condition - but there is.
> This is the query:
>
> SELECT documents.id, title, raw_data, q, ts_rank(fts_data, q, 4) AS
> rank, html_filename
>              FROM documents, to_tsquery('document') AS q
>              WHERE fts_data @@ q
>           ORDER BY rank DESC  LIMIT 25;

It's the ranking that's causing the detoasting. "ts_rank(fts_data, q, 
4)" has to fetch the contents of the fts_data column.

Sorry, I was confused earlier: the "Recheck Cond:" line is always there 
in the EXPLAIN output of bitmap index scans, even if the recheck 
condition is never executed at runtime. It's because the executor has to 
be prepared to run the recheck-condition, if the bitmap grows large 
enough to become "lossy", so that it only stores the page numbers of 
matching tuples, not the individual tuples

- Heikki


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance