Thread: 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