| 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 |
