WindowAgg optimized out of subquery but not out of CTE, generating different results.

From: bym(at)byeh(dot)net
To: pgsql-general(at)postgresql(dot)org
Subject: WindowAgg optimized out of subquery but not out of CTE, generating different results.
Date: 2017-01-28 04:30:12
Message-ID: 3fd17a5716b414b3a6b1490775dcfb43214c6002@webmail.byeh.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am having an issue where the subquery version of a query and the CTE
version of a query are generating different results.
Postgresql version 9.5.5 on Linux Ubuntu 14.04.

Table definition:
      Table "dealerinventorychange"
    Column     |     Type     |    
Modifiers     
---------------+--------------+--------------------
 dealerid      | integer      | not null
 modelid       | integer      | default 0
 trimid        | integer      | not null default 0
 inventorydate | date         | not null
 sold          | integer      | default 0
 arrived       | integer      | default 0
 inventory     | integer      | default 0
Indexes:
    "dealerinventorychange_pkey" PRIMARY KEY, btree (dealerid,
trimid, inventorydate)

So I have this query that involves a window function to select the
most recent rows of given criteria on or before a date from this table
of about 4M rows.
For the modelid, dealerid, and inventorydate filter criteria in this
particular example query, 2 different trimids and 8 total rows from
the table should be selected for the window function to operate on.
The intent of the window function used in combination with DISTINCT ON
is to select the most recent inventorydate row for each trimid that is
no later than the cutoff date.

The CTE version of the query plan selects the correct two rows to sum:
with foo as (
      select distinct on (dealerid, trimid)
                  dealerid, trimid, inventorydate,
sold, arrived, inventory,
                  first_value(inventorydate) over
(partition by dealerid, trimid order by inventorydate desc range
between unbounded preceding and unbounded following) as closest_date
      from dealerinventorychange where dealerid=21358 and modelid
= 2272 and inventorydate <= '2016-12-31')
select sum(inventory) from foo;

Result:
 sum
-----
   9
(1 row)

The CTE query generates the following query plan:
                                                                            
QUERY
PLAN                                                                            

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4100.59..4100.60 rows=1 width=4) (actual
time=2.739..2.739 rows=1 loops=1)
   CTE foo
     ->  Unique  (cost=4100.56..4100.57 rows=1 width=37) (actual
time=2.719..2.733 rows=2 loops=1)
           ->  WindowAgg  (cost=4100.56..4100.57 rows=1
width=37) (actual time=2.717..2.730 rows=8 loops=1)
                 ->  Sort  (cost=4100.56..4100.56
rows=1 width=37) (actual time=1.388..1.389 rows=8 loops=1)
                       Sort Key:
dealerinventorychange.trimid, dealerinventorychange.inventorydate DESC
                       Sort Method: quicksort 
Memory: 25kB
                       ->  Bitmap Heap Scan on
dealerinventorychange  (cost=50.15..4100.56 rows=1 width=37) (actual
time=1.294..1.375 rows=8 loops=1)
                             Recheck Cond:
((dealerid = 21358) AND (inventorydate <= '2016-12-31'::date))
                             Filter:
(modelid = 2272)
                             Rows Removed
by Filter: 1840
                             Heap Blocks:
exact=209
                             ->  Bitmap
Index Scan on dealerinventorychange_pkey  (cost=0.00..50.15 rows=2737
width=0) (actual time=0.679..0.679 rows=5249 loops=1)
                                  
Index Cond: ((dealerid = 21358) AND (inventorydate <=
'2016-12-31'::date))
   ->  CTE Scan on foo  (cost=0.00..0.02 rows=1 width=4) (actual
time=2.721..2.735 rows=2 loops=1)
 Planning time: 0.161 ms
 Execution time: 3.979 ms
(17 rows)

The subquery version of the query selects the wrong two rows to sum.
select sum(inventory) from
      (select distinct on (dealerid, trimid)
                  dealerid, trimid, inventorydate,
sold, arrived, inventory,
                  first_value(inventorydate) over
(partition by dealerid, trimid order by inventorydate desc range
between unbounded preceding and unbounded following) as closest_date
      from dealerinventorychange where dealerid=21358 and modelid
= 2272 and inventorydate <= '2016-12-31')
as foo;

Result:
sum
-----
   2
(1 row)

The subquery query generates the following query plan:
                                                                        
QUERY
PLAN                                                                        

------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4100.57..4100.58 rows=1 width=4) (actual
time=1.981..1.981 rows=1 loops=1)
   ->  Unique  (cost=4100.56..4100.56 rows=1 width=16) (actual
time=1.969..1.977 rows=2 loops=1)
         ->  Sort  (cost=4100.56..4100.56 rows=1 width=16)
(actual time=1.966..1.968 rows=8 loops=1)
               Sort Key: dealerinventorychange.trimid
               Sort Method: quicksort  Memory: 25kB
               ->  Bitmap Heap Scan on
dealerinventorychange  (cost=50.15..4100.56 rows=1 width=16) (actual
time=1.812..1.956 rows=8 loops=1)
                     Recheck Cond: ((dealerid =
21358) AND (inventorydate <= '2016-12-31'::date))
                     Filter: (modelid = 2272)
                     Rows Removed by Filter: 1840
                     Heap Blocks: exact=209
                     ->  Bitmap Index Scan on
dealerinventorychange_pkey  (cost=0.00..50.15 rows=2737 width=0)
(actual time=0.843..0.843 rows=5249 loops=1)
                           Index Cond:
((dealerid = 21358) AND (inventorydate <= '2016-12-31'::date))
 Planning time: 0.245 ms
 Execution time: 2.052 ms
(14 rows)

The subquery version appears to optimize out the WindowAgg and
simplifies the sort to just on trimid for some reason, and thus
selects the wrong two rows for the Unique step.
Without the sort by inventorydate, the query picked the two
inventorydates furthest from the cutoff date (in this instance).
Is this supposed to happen given the queries above? Why? Is there some
sort of interaction happening between the missing DISTINCT ON ORDER BY
and the window ORDER BY? is this a bug?

If I add filter criteria in the outer query to force the right rows to
be selected by the outer query in the subquery version, I get the
following query plan:

select sum(inventory) from
      (select distinct on (dealerid, trimid)
                  dealerid, trimid, inventorydate,
sold, arrived, inventory,
                  first_value(inventorydate) over
(partition by dealerid, trimid order by inventorydate desc range
between unbounded preceding and unbounded following) as closest_date
      from dealerinventorychange where dealerid=21358 and modelid
= 2272 and inventorydate <= '2016-12-31')
as foo
where inventorydate = closest_date;

Result:
sum
-----
   9
(1 row)

                                                                              
QUERY
PLAN                                                                              

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4100.58..4100.59 rows=1 width=4) (actual
time=1.639..1.639 rows=1 loops=1)
   ->  Subquery Scan on foo  (cost=4100.56..4100.58 rows=1
width=4) (actual time=1.614..1.634 rows=2 loops=1)
         Filter: (foo.inventorydate = foo.closest_date)
         ->  Unique  (cost=4100.56..4100.57 rows=1 width=16)
(actual time=1.610..1.630 rows=2 loops=1)
               ->  WindowAgg  (cost=4100.56..4100.57
rows=1 width=16) (actual time=1.609..1.626 rows=8 loops=1)
                     ->  Sort 
(cost=4100.56..4100.56 rows=1 width=16) (actual time=1.602..1.603
rows=8 loops=1)
                           Sort Key:
dealerinventorychange.trimid, dealerinventorychange.inventorydate DESC
                           Sort Method:
quicksort  Memory: 25kB
                           ->  Bitmap Heap
Scan on dealerinventorychange  (cost=50.15..4100.56 rows=1 width=16)
(actual time=1.377..1.547 rows=8 loops=1)
                                
Recheck Cond: ((dealerid = 21358) AND (inventorydate <=
'2016-12-31'::date))
                                
Filter: (modelid = 2272)
                                 Rows
Removed by Filter: 1840
                                 Heap
Blocks: exact=209
                                 -> 
Bitmap Index Scan on dealerinventorychange_pkey  (cost=0.00..50.15
rows=2737 width=0) (actual time=0.518..0.518 rows=5249 loops=1)
                                      
Index Cond: ((dealerid = 21358) AND (inventorydate <=
'2016-12-31'::date))
 Planning time: 0.161 ms
 Execution time: 1.686 ms
(17 rows)

The WindowAgg comes back now after I put the filter on the outer
query. Why?

This behavior in postgres is confusing the heck out of me.
Thanks in advance to anyone who can explain this discrepancy.
B. Yeh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2017-01-28 05:08:51 Re: Recovery Assistance
Previous Message Brian Mills 2017-01-28 01:40:20 Re: Recovery Assistance