SELECT ∗ and TOAST

I have already blogged about
TOAST storage (and
TOAST queries), but I would like to highlight one aspect that is
easily overlooked by application programmers.
There is an age-old debate over whether using SELECT ∗ is good programming practice — many feel that wildcarding all
columns of a table makes applications more brittle when columns are added or removed, while others feel that specifying all columns in an
application is more error-prone. If a table has only a few columns, specifying them is pretty easy, but when the number of columns is
large, specification of column names can be cumbersome. Of course, having wide rows in tables has its own issues, but I don't want to get
into that here — what I do want to cover is its affect on TOAST'ed values.

As I mentioned in my previous blog post, long values are not stored in the main row, but in TOAST tables that have additional access
overhead. This is great when you only need the short values from a row, which is typical, but what happens when you use SELECT
∗? Well, if you actually need all the column values, using SELECT ∗ or specifying the column name explicitly makes no
difference, but many applications use SELECT ∗ when they only need some of the column values, and this is where TOAST becomes
an issue. If you don't need to see some columns, and those columns might be long and hence TOAST'ed, it is wise to avoid selecting them
in a query — this will avoid TOAST table access and speed up queries significantly.

Continue Reading »

SELECT ∗ and TOAST
By  , Apr 13, 2012

I have already blogged about
TOAST storage (and
TOAST queries), but I would like to highlight one aspect that is
easily overlooked by application programmers.
There is an age-old debate over whether using SELECT ∗ is good programming practice — many feel that wildcarding all
columns of a table makes applications more brittle when columns are added or removed, while others feel that specifying all columns in an
application is more error-prone. If a table has only a few columns, specifying them is pretty easy, but when the number of columns is
large, specification of column names can be cumbersome. Of course, having wide rows in tables has its own issues, but I don't want to get
into that here — what I do want to cover is its affect on TOAST'ed values.

As I mentioned in my previous blog post, long values are not stored in the main row, but in TOAST tables that have additional access
overhead. This is great when you only need the short values from a row, which is typical, but what happens when you use SELECT
∗? Well, if you actually need all the column values, using SELECT ∗ or specifying the column name explicitly makes no
difference, but many applications use SELECT ∗ when they only need some of the column values, and this is where TOAST becomes
an issue. If you don't need to see some columns, and those columns might be long and hence TOAST'ed, it is wise to avoid selecting them
in a query — this will avoid TOAST table access and speed up queries significantly.

Continue Reading »

Tags: