Thread: Performance of column order
Hallo! Do anybody know if the order of the columns in a table has affect on the performance? Should I use length-variable colums like TEXT, BLOB,... alfter length-static colums like INTEGER, BOOLEAN, ...? Is there a advantage to create a separate table for BLOBS and TEXT joining the main table about a primary key? Thanks for help Jörg Sommer -------------------------------------------------------------------------- Sommer Maschinenbau Pagenstecherstr. 146 49090 Osnabrück 0049 (0)541 125085 0049 (0)541 129557 Service@Sommer-Maschinenbau.de --------------------------------------------------------------------------
Egon Sommer <Service@Sommer-Maschinenbau.de> writes:
> Should I use length-variable colums like TEXT, BLOB,... alfter length-static colums like INTEGER, BOOLEAN, ...?
There is some marginal advantage to that, but I wouldn't recommend
contorting your application to do it. In particular, AFAIR it only
helps in a row that has no NULLs. (Check the heap_getattr code for
the gory details.)
> Is there a advantage to create a separate table for BLOBS and TEXT joining the main table about a primary key?
No ... TOAST more or less does that for you.
regards, tom lane
Egon Sommer wrote: > Hallo! > > Do anybody know if the order of the columns in a table has affect > on the performance? > > Should I use length-variable colums like TEXT, BLOB,... alfter > length-static colums like INTEGER, BOOLEAN, ...? It is very slightly faster to have variable length stuff after fixed length fields. > Is there a advantage to create a separate table for BLOBS and > TEXT joining the main table about a primary key? Probably, though TOAST stores large stuff in backup tables anyway, so there is probably little difference. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026