Обсуждение: performance and number of selected columns

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.

Dirk

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