Обсуждение: 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