Message-Id: <5.1.0.14.0.20030611092555.02ff5e80@mailserver.ipl.co.uk>
Date: Wed, 11 Jun 2003 09:35:16 +0100
To: pgsql-sql@postgresql.org
From: Hilary Forbes <hforbes@dmr.co.uk>
Subject: Record size
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

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.

Now the question.  Does the number of fields that you have in a record and indeed their size affect performance on a SELECT statement 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 be affected by the fact that f4 is large in physical size?  If so, I would be better having two parallel tables one with fields f1,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

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