Order of columns in query is important?!

From: "Colin 't Hart" <colin(at)sharpheart(dot)org>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Order of columns in query is important?!
Date: 2015-05-25 09:26:49
Message-ID: CAMon-aReV3F4Kn8cN3OhJPe-U1ACkkzzDdOwgtR29yPaA3TmZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Guillaume Lelarge 2015-05-25 09:30:35 Re: Strange replication problem - segment restored from archive but still requested from master
Previous Message alex2010 2015-05-25 09:13:34 about lob(idea)