Re: Order of columns in query is important?!

Поиск
Список
Период
Сортировка
От CK Tan
Тема Re: Order of columns in query is important?!
Дата
Msg-id CAJNt7=bn3KwQ0q8yE-3LfTsTMAQSE7kODV=DC0rx33-nHhN5sw@mail.gmail.com
обсуждение исходный текст
Ответ на Order of columns in query is important?!  ("Colin 't Hart" <colin@sharpheart.org>)
Ответы Re: Order of columns in query is important?!  (Petr Jelinek <petr@2ndquadrant.com>)
Re: Order of columns in query is important?!  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
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@sharpheart.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Paul Smith
Дата:
Сообщение: Re: ERROR: MultiXactId xxxx has not been created yet -- apparent wraparound
Следующее
От: Petr Jelinek
Дата:
Сообщение: Re: Order of columns in query is important?!