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?!
Re: Order of columns in query is important?! |
| Список | 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 по дате отправления: