Message-ID: <53560EE5.3080300@vmware.com>
Date: Tue, 22 Apr 2014 09:40:37 +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>,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: tsearch2, large data and indexes
References: 
 <CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com>
In-Reply-To: 
 <CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 04/20/2014 02:15 AM, Ivan Voras wrote:
> Hello,
>
> If a table contains simple fields as well as large (hundreds of KiB)
> text fields, will accessing only the simple fields cause the entire
> record data, including the large fields, to be read and unpacked?
> (e.g. SELECT int_field FROM table_with_large_text)

No.

> 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?

The large fields are stored in the toast table. You can check if the 
toast table is accessed with a query like this:

select * from pg_stat_all_tables where relid = (select reltoastrelid 
from pg_class where relname='table');

Run that before and after your query, and see if the numbers change.

- Heikki


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