tsearch2, large data and indexes

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: tsearch2, large data and indexes
Date: 2014-04-19 23:15:25
Message-ID: CAF-QHFUaRk-v4bnTUQ0_eepH0MEKaCaVM-fwiTP1uxM8EgKQVw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

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? That would be
very wasteful since data is practically duplicated in the table and in
the index. Any way around it?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sergey Konoplev 2014-04-19 23:41:56 Re: Hot standby 9.2.1 PANIC: WAL contains references to invalid pages
Previous Message Wureka JI 2014-04-18 08:33:31 Help on migrating data from MSSQL2008R2 to PostgreSQL 9.3