performance and number of selected columns

Dirk Lutzebaeck writes:

Hi,

say I have three tables a,b,c with lots of columns (say 10 for each
table). Using psql command line, why does

SELECT a.*,b.*,c.* FROM a,b,c

takes much more longer (in my specifc case 3 times) than only
selecting one column like

SELECT a.oid, b.oid, c.oid FROM a,b,c.

This is on 7.0. Can I play with buffer sizes? Indexes exist on most
of the columns. Columns in the second line can be arbitrary.

Two things two add:

- EXPLAIN shosw the same results in both cases
- currently I was working with ~ 500 rows

Dirk

Dirk Lutzebaeck <lutzeb@aeccom.com> writes:

say I have three tables a,b,c with lots of columns (say 10 for each
table). Using psql command line, why does
SELECT a.*,b.*,c.* FROM a,b,c
takes much more longer (in my specifc case 3 times) than only
selecting one column like
SELECT a.oid, b.oid, c.oid FROM a,b,c.

I'd guess you are simply looking at the increased time to transfer
the additional data to the frontend, format it for display, etc.
With a pure cartesian-product join like that (no WHERE), the backend
is basically going to be spitting out tuples as fast as it can read
them from disk...

regards, tom lane