pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerform scan on Toast table
2+ messages / 2 participants
[nested] [flat]
* Perform scan on Toast table
@ 2012-12-06 03:08 classical_89 <luongnx512@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: classical_89 @ 2012-12-06 03:08 UTC (permalink / raw)
To: pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Perform scan on Toast table
@ 2012-12-10 08:57 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: classical_89 <luongnx512@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Albe Laurenz @ 2012-12-10 08:57 UTC (permalink / raw)
To: classical_89 *EXTERN* <luongnx512@gmail.com>; 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-12-10 08:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-06 03:08 Perform scan on Toast table classical_89 <luongnx512@gmail.com>
2012-12-10 08:57 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox