Complete row is fetched ?
I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?
Satish
Satish Burnwal (sburnwal) wrote:
I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?
yes and no.
a row can consist of both a proper tuple in an 8K block, and toast data
stored in toast tables. the whole block that the tuple is in will be
read into the shared_buffers space, however, toast data thats not
referenced will not be fetched. toast is used for larger fields that
won't fit in a single block.
for more info on toast, see
http://www.postgresql.org/docs/current/static/storage-toast.html
In response to Satish Burnwal (sburnwal) :
I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?
Depends, large columns (TEXT, BYTA) are TOASTed�, that means, the content
of this columns stored in a other table. So it is always a good idea to
specify all rows you needed and don't use select *.
� http://www.postgresql.org/docs/8.4/interactive/storage-toast.html
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
A. Kretschmer wrote:
In response to Satish Burnwal (sburnwal) :
I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?Depends, large columns (TEXT, BYTA) are TOASTed�, that means, the content
of this columns stored in a other table. So it is always a good idea to
specify all rows you needed and don't use select *.� http://www.postgresql.org/docs/8.4/interactive/storage-toast.html
Another reason to prefer explicit column lists over SELECT * for queries
on relations with a lot of columsn and rows, is that it enables some
plan nodes to be more economical with memory, see e.g. comment of
'disuse_physical_tlist':
/*
* disuse_physical_tlist
* Switch a plan node back to emitting only Vars actually referenced.
*
* If the plan node immediately above a scan would prefer to get only
* needed Vars and not a physical tlist, it must call this routine to
* undo the decision made by use_physical_tlist(). Currently, Hash, Sort,
* and Material nodes want this, so they don't have to store useless columns.
*/
regards,
Yeb Havinga
On 16/04/2010 07:11, John R Pierce wrote:
Satish Burnwal (sburnwal) wrote:
I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?yes and no.
a row can consist of both a proper tuple in an 8K block, and toast data
stored in toast tables. the whole block that the tuple is in will be
read into the shared_buffers space, however, toast data thats not
referenced will not be fetched. toast is used for larger fields that
won't fit in a single block.
On a related note, what happens when you do something like this? -
select count(*) ....
Does any data actually get read?
Is there any difference internally to saying "count(1)" instead?
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
In response to Raymond O'Donnell :
On a related note, what happens when you do something like this? -
select count(*) ....
Does any data actually get read?
No, it check's only the visibility for each record -> seq-scan.
Is there any difference internally to saying "count(1)" instead?
No, it's the same execution plan.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 16/04/10 16:23, A. Kretschmer wrote:
In response to Raymond O'Donnell :
On a related note, what happens when you do something like this? -
select count(*) ....
Does any data actually get read?
No, it check's only the visibility for each record -> seq-scan.
... though in practice with OS and disk readahead this probably means
all the data actually gets read from disk, though PostgreSQL doesn't
have to process all of it.
I sometimes wonder if being able to store visibility info externally to
a tuple in a separate file - in condensed fixed-width form - would be
useful for performance, especially where the table has quite wide tuples
with types that are big-ish but not TOASTable. Sure, it'd be more disk
seeking but OTOH it'd be more likely to stick around in cache, could
even be put on other storage, etc.
I suspect that even testing the notion out would involve ripping out and
rewriting half of Pg's guts, though, so it's pretty much hot air anyway.
--
Craig Ringer
Craig Ringer wrote:
I sometimes wonder if being able to store visibility info externally
to a tuple in a separate file - in condensed fixed-width form - would
be useful for performance, especially where the table has quite wide
tuples with types that are big-ish but not TOASTable. Sure, it'd be
more disk seeking but OTOH it'd be more likely to stick around in
cache, could even be put on other storage, etc.
In this situation, you can always split the primary key and the other
most referenced fields out to a "thinner" version, then only join
against the rest when needed. The rows really do have to be just on the
edge of TOAST size for this to ever make sense though, given how much
overhead is taken per row just to store anything in the database.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us