Обсуждение: Record size

Firstly an apology - I think this should be posted to the performance mailing list but can anyone tell me how to
subscribeto this please?  The usual form to fill in is not available on the website.  Hopefully others in this list
willfind the answer of use though.
 

Now the question.  Does the number of fields that you have in a record and indeed their size affect performance on a
SELECTstatement when only a subset of fields is being extracted?  So suppose that I have
 

f1      integer
f2      varchar(4)
f3      varchar(20)
f4      text

and f4 contains reams of data.  (well eg 4k).  If I just want f1 and f2, will the performance of the SELECT statement
beaffected by the fact that f4 is large in physical size?  If so, I would be better having two parallel tables one with
fieldsf1,f2,f3 and one with f1,f4 as most of the time I don't want to read in the f4.
 

As a secondary question, presumably it is better to have a permanently compiled view in the database defined as

CREATE VIEW myview as SELECT f1,f2,f3 from mytable

rather than issuing the query each time direct to the underlying table?

Many thanks in advance.  Oh, BTW, we are still in the dark ages with v7.0 but will be upgrading. :-)
Hilary





Hilary Forbes
-------------
DMR Computer Limited:   http://www.dmr.co.uk/
Direct line:  01689 889950
Switchboard:  (44) 1689 860000  Fax: (44) 1689 860330
E-mail:  hforbes@dmr.co.uk

**********************************************************

Hilary Forbes <hforbes@dmr.co.uk> writes:
> Now the question.  Does the number of fields that you have in a record and indeed their size affect performance on a
SELECTstatement when only a subset of fields is being extracted?  So suppose that I have
 

> f1      integer
> f2      varchar(4)
> f3      varchar(20)
> f4      text

> and f4 contains reams of data.  (well eg 4k).  If I just want f1 and f2, will the performance of the SELECT statement
beaffected by the fact that f4 is large in physical size?  If so, I would be better having two parallel tables one with
fieldsf1,f2,f3 and one with f1,f4 as most of the time I don't want to read in the f4.
 

Most of the possible benefit applies automatically, because large values
of f4 will be "toasted" (moved out of line).  I don't think it's worth
contorting your table structure for.  You might care to run some
experiments to verify that theory, though.  (But update first;
experiments against 7.0 don't necessarily prove anything about 7.3 ...)

> As a secondary question, presumably it is better to have a permanently compiled view in the database defined as

> CREATE VIEW myview as SELECT f1,f2,f3 from mytable

> rather than issuing the query each time direct to the underlying table?

There's essentially no difference in performance.  Views are not
pre-optimized.
        regards, tom lane

Hilary,

> Firstly an apology - I think this should be posted to the performance
> mailing list but can anyone tell me how to subscribe to this please?  The
> usual form to fill in is not available on the website.  Hopefully others in
> this list will find the answer of use though.

To: Majordomo@postgresql.org
Re:
Message: subscribe pgsql-performance

> Many thanks in advance.  Oh, BTW, we are still in the dark ages with v7.0
> but will be upgrading. :-) Hilary

I'd suggest upgrading first before doing any performance tuning.  Postgres 
performance and tunability has improved worlds since 7.0; it's quite possible 
that optimization workarounds you make in 7.0 will actually harm performance 
in 7.3 or 7.4.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco