Re: Order of columns in query is important?!

It has to do with the implementation of slot_getattr, which tries to do the
deform on-demand lazily.

if you do select a,b,c, the execution would do slot_getattr(1) and deform
a, and then slot_getattr(2) which reparse the tuple to deform b, and
finally slot_getattr(3), which parse the tuple yet again to deform c.

Where as if you do select c, b, a, it would do slot_getattr(3) to deform c,
and in the process deform a and b in one pass. Subsequent calls to
slot_getattr 1 and 2 would find the attribute ready and available, and
return it (without parsing the tuple again).

For Vitesse X, we mark all columns that were required in the query during
JIT compile, and deform it in one shot. PG should be able to do the same.

-cktan

On Mon, May 25, 2015 at 2:26 AM, Colin 't Hart <colin(at)sharpheart(dot)org> wrote:

> 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>