From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Manvendra <manvendra2525(at)gmail(dot)com> |
Cc: | Kieran McCusker <kieran(dot)mccusker(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #16548: Order by on array element giving disparity in result |
Date: | 2020-07-21 15:10:31 |
Message-ID: | CA+bJJbxgCHQT2wi_sPW-rfd7Z23t7hu=Vqxpe09G233NdLKn=A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Manvendra:
On Tue, Jul 21, 2020 at 4:47 PM Manvendra <manvendra2525(at)gmail(dot)com> wrote:
> Alright! Just wanted to know how limit works here - How limit is showing the different output
> postgres=# select * from bint order by a[2] desc limit 5;
> a
> ---------------
> {14}
> {10}
> {14}
> {10,14,10,10} <-- It comes prior to 5th record and consistently whereas " select * from bint order by a[2] desc;" showing something else consistently.
> {10,14,14,14}
> (5 rows)
It comes in a different order because your query does not fully order
the rows, either with or without limit.
Your order field, a[2], is null, null, null, 14, 14, 14. The server is
free to shuffle the set of rows in any order in the groups which have
the same value for a[2]. It does not shuffle, as it would be a waste
of time, but it is also free to do the following:
1.- Without limit: build the result set and use a quick sort. Or do an
index scan. Or do a stable merge sort. Or a heap sort.
2.- With limit: Scan the rows keeping the top 5 ( this is easy to do
with a heap ), no need to keep all the rows ( I think this comes out
as top-n heapsort or something similar in EXPLAIN ).
It is like what happens if I handle you a shuffled deck of cards and
tell you to order by rank, You will produce 4 aces, for deuces, ...
but the suits will be unordered in each group ( unless you decide to
work extra ). Also, if I ask you to pick the top 6 you may just spread
them on the tabla and handle me the four aces and two deuces, but they
may be in a different order than what you returned on the first
problem as you used a different method more suited for this problem.
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume de Rorthais | 2020-07-21 15:34:57 | Re: Buffers from parallel workers not accumulated to upper nodes with gather merge |
Previous Message | David G. Johnston | 2020-07-21 14:18:23 | Re: Bug - 11.8 |