Re: Perform scan on Toast table - Mailing list pgsql-performance

From Albe Laurenz
Subject Re: Perform scan on Toast table
Date
Msg-id A737B7A37273E048B164557ADEF4A58B05787686@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to Perform scan on Toast table  (classical_89 <luongnx512@gmail.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Why is PostgreSQL 9.2 slower than 9.1 in my tests?
Next
From:
Date:
Subject: Re: Slow query: bitmap scan troubles