Thread: Perform scan on Toast table

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.

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