Re: Perform scan on Toast table

classical_89 wrote:
> 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 ?

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