Re: Order of columns in query is important?!

On 2015-05-25 PM 06:26, Colin 't Hart wrote:
>
> 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?
>

I think any difference may have to do with an extra projection step on top of
the underlying scan when the target list does not match the tuple descriptor.
When that happens there has to happen additional processing in Sort data
initialization which converts the data back (from values[], nulls[] lists
form) to a form that sorting code expects/understands.

That means the specified order of columns in a query does matter which would
have to match the defined order in order to avoid extra processing (that is
only when specified columns *exactly* matches the tuple descriptor).

Thanks,
Amit