From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Ivan Voras <ivoras@freebsd.org>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: tsearch2, large data and indexes
Date: Tue, 22 Apr 2014 09:40:37 +0300
Message-ID: <53560EE5.3080300@vmware.com> (raw)
In-Reply-To: <CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com>
References: <CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: hlinnakangas@vmware.com, ivoras@freebsd.org
Subject: Re: tsearch2, large data and indexes
In-Reply-To: <53560EE5.3080300@vmware.com>
* 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