Hi,
I hope this is the best place to report this or should I be on
pgsql-general or pgsql-bugs?
It seems that the order of columns in a query can make a difference in
execution times.
In my brief investigation, queries on table(a,b,c,d,e,f,g,h) of the form
select * from table order by non-indexed-column limit 25;
select a,b,c,d,e,f,g,h from table order by non-indexed-column limit 25;
performed the same (approx 1.5 seconds on our customers table --
rows=514431 width=215), while the query
select h,g,f,e,d,c,b,a from table order by non-indexed-column limit 25;
was about 50% slower (approx 2.2 seconds on our customers table).
I had expected these to perform the same -- to my mind column ordering
in a query should be purely presentation -- as far as I'm concerned,
the DBMS can retrieve the columns in a different order as long as it
displays it in the order I've asked for them. Although, again, the
order of columns in a resultset in a Java or Python is mostly
irrelevant, though when displayed in psql I'd want the columns in the
order I asked for them.
Is there really something strange happening here? Or perfectly
explainable and expected?
Regards,
Colin