Perform scan on Toast table

[classical_89]

Hi everyone ,I have a question. I have a table with large data (i was used
bytea datatype and insert a binary content to table ) so that Postgres help
me get a TOAST table to storage out-of-line values .
Assume that my table is " tbl_test " and toast table oid is 16816

When i peform EXPLAIN ANALYZE select query on tbl_test ( EXPLAIN ANALYZE
SELECT * FROM tbl_test).It show that sequential scan was performed on
tbl_test ,but when i check pg_toast table with this query :


SELECT
relid,
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
FROM pg_stat_all_tables
WHERE relid IN ( SELECT oid
FROM pg_class
WHERE relkind = 't' ) AND relid = 16816

I saw that seq_tup_read = 0 and the seq_scan is always is 1 .idx_scan is
increase arcording to the number of query on tbl_test

I was wordering : Do have a sequential scan perform on tbl_test and other
index scan will be peforming on TOAST after this sequential scan ?
Can you explain this dump question to me ,please ?
P/S : sorry for my bad English .Thanks ! :)








--
View this message in context: http://postgresql.1045698.n5.nabble.com/Perform-scan-on-Toast-table-tp5735406.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

[Albe Laurenz]

Post by classical_89
Hi everyone ,I have a question. I have a table with large data (i was used
bytea datatype and insert a binary content to table ) so that Postgres help
me get a TOAST table to storage out-of-line values .
Assume that my table is " tbl_test " and toast table oid is 16816
When i peform EXPLAIN ANALYZE select query on tbl_test ( EXPLAIN ANALYZE
SELECT * FROM tbl_test).It show that sequential scan was performed on
SELECT
relid,
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
FROM pg_stat_all_tables
WHERE relid IN ( SELECT oid
FROM pg_class
WHERE relkind = 't' ) AND relid = 16816
I saw that seq_tup_read = 0 and the seq_scan is always is 1 .idx_scan is
increase arcording to the number of query on tbl_test
I was wordering : Do have a sequential scan perform on tbl_test and other
index scan will be peforming on TOAST after this sequential scan ?
Can you explain this dump question to me ,please ?

The entries in the TOAST table need not be in the same order
as the entries in the main table. So if you'd fetch them
sequentially, you'd have to reorder them afterwards.

It seems logical that access via the TOAST index is cheaper.

Yours,
Laurenz Albe