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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
