Re: Question about sorting internals

On Wed, Dec 11, 2013 at 03:34:38PM +0530, Ashutosh Bapat wrote:
> Hi deepesz,
> You might want to see their EXPLAIN VERBOSE outputs. Having one of them
> (2004 one) lesser number of rows, might be getting picked up as first
> relation being union and thus ends up having it's rows before the second
> one. Explain output would make it more clear. Also, try having same number
> of rows in both the relations.

Explains:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Unique (cost=0.44..0.48 rows=9 width=12) (actual time=0.030..0.035 rows=6 loops=1)
Output: rok2004.miesiac, (2004), rok2004.wynik
CTE rok2004
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=8) (actual time=0.001..0.003 rows=5 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
CTE rok2005
-> Values Scan on "*VALUES*_1" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.001 rows=4 loops=1)
Output: "*VALUES*_1".column1, "*VALUES*_1".column2
-> Sort (cost=0.32..0.35 rows=9 width=12) (actual time=0.029..0.031 rows=9 loops=1)
Output: rok2004.miesiac, (2004), rok2004.wynik
Sort Key: rok2004.miesiac, rok2004.wynik
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..0.18 rows=9 width=12) (actual time=0.007..0.018 rows=9 loops=1)
-> CTE Scan on rok2004 (cost=0.00..0.10 rows=5 width=12) (actual time=0.006..0.011 rows=5 loops=1)
Output: rok2004.miesiac, 2004, rok2004.wynik
-> CTE Scan on rok2005 (cost=0.00..0.08 rows=4 width=12) (actual time=0.002..0.004 rows=4 loops=1)
Output: rok2005.miesiac, 2005, rok2005.wynik
Total runtime: 0.077 ms
(18 rows)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Unique (cost=0.44..0.48 rows=9 width=12) (actual time=0.024..0.027 rows=6 loops=1)
Output: rok2005.miesiac, (2005), rok2005.wynik
CTE rok2004
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=8) (actual time=0.001..0.003 rows=5 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
CTE rok2005
-> Values Scan on "*VALUES*_1" (cost=0.00..0.05 rows=4 width=8) (actual time=0.001..0.003 rows=4 loops=1)
Output: "*VALUES*_1".column1, "*VALUES*_1".column2
-> Sort (cost=0.32..0.35 rows=9 width=12) (actual time=0.023..0.024 rows=9 loops=1)
Output: rok2005.miesiac, (2005), rok2005.wynik
Sort Key: rok2005.miesiac, rok2005.wynik
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..0.18 rows=9 width=12) (actual time=0.004..0.015 rows=9 loops=1)
-> CTE Scan on rok2005 (cost=0.00..0.08 rows=4 width=12) (actual time=0.003..0.006 rows=4 loops=1)
Output: rok2005.miesiac, 2005, rok2005.wynik
-> CTE Scan on rok2004 (cost=0.00..0.10 rows=5 width=12) (actual time=0.001..0.006 rows=5 loops=1)
Output: rok2004.miesiac, 2004, rok2004.wynik
Total runtime: 0.053 ms
(18 rows)

So, it looks like rowcount is the one thing that's different. Not
entirely sure how the logic would be to make rowcount differ.

After some more talk on #postgresql, it looks like I will have to spend
some time with debugger to see what's happening there.

Best regards,

depesz