Re: tsearch2, large data and indexes

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