pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Record size 3+ messages / 3 participants [nested] [flat]
* Record size @ 2003-06-11 08:35 Hilary Forbes <hforbes@dmr.co.uk> 0 siblings, 2 replies; 3+ messages in thread From: Hilary Forbes @ 2003-06-11 08:35 UTC (permalink / raw) To: pgsql-sql 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 ********************************************************** ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Record size @ 2003-06-11 13:11 Tom Lane <tgl@sss.pgh.pa.us> parent: Hilary Forbes <hforbes@dmr.co.uk> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2003-06-11 13:11 UTC (permalink / raw) To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-sql 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 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. 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Record size @ 2003-06-11 16:18 Josh Berkus <josh@agliodbs.com> parent: Hilary Forbes <hforbes@dmr.co.uk> 1 sibling, 0 replies; 3+ messages in thread From: Josh Berkus @ 2003-06-11 16:18 UTC (permalink / raw) To: Hilary Forbes <hforbes@dmr.co.uk>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-11 16:18 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-06-11 08:35 Record size Hilary Forbes <hforbes@dmr.co.uk> 2003-06-11 13:11 ` Tom Lane <tgl@sss.pgh.pa.us> 2003-06-11 16:18 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox