Push-down does not work, if we use CTEs to materialize temporal primitive sub-queries
Created by: Piiit
The filter Filter: (r_1.a = 'A'::bpchar)
gets pushed down into sub-selects if we do not use any CTEs.
That's good, since we calculate temporal primitives with less rows, sorting out any unwanted rows as fast as possible.
A=# explain analyze select * from (select * from (r align r r2 on r.a=r2.a with (ts,te,ts,te)) where ts=4 and a='A') x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on r (cost=96.00..96.74 rows=1 width=24) (actual time=0.028..0.028 rows=0 loops=1)
Filter: (r.ts = 4)
-> Adjustment(for ALIGN) (cost=96.00..96.44 rows=24 width=40) (actual time=0.028..0.028 rows=0 loops=1)
-> Sort (cost=96.00..96.02 rows=8 width=40) (actual time=0.026..0.026 rows=0 loops=1)
Sort Key: r_1.rn, (GREATEST(r_1.ts, r2.ts)), (LEAST(r_1.te, r2.te))
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.00..95.88 rows=8 width=40) (actual time=0.014..0.014 rows=0 loops=1)
Join Filter: ((r_1.ts < r2.te) AND (r_1.te > r2.ts) AND (r_1.a = r2.a))
-> Subquery Scan on r_1 (cost=0.00..64.95 rows=8 width=32) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (r_1.a = 'A'::bpchar)
Rows Removed by Filter: 3
-> WindowAgg (cost=0.00..45.33 rows=1570 width=24) (actual time=0.009..0.010 rows=3 loops=1)
-> Seq Scan on r r_2 (cost=0.00..25.70 rows=1570 width=24) (actual time=0.004..0.005 rows=3 loops=1)
-> Materialize (cost=0.00..29.66 rows=8 width=16) (never executed)
-> Seq Scan on r r2 (cost=0.00..29.62 rows=8 width=16) (never executed)
Filter: (a = 'A'::bpchar)
Planning time: 0.113 ms
Execution time: 0.058 ms
(18 rows)
If we use CTEs instead, the filter Filter: (r_1.a = 'A'::bpchar)
is on top, resulting in a late filtering. Therefore, we must handle all unwanted rows inside the temporal primitives before we can sort them out. That is slower!
A=# explain analyze with r as (with r as ( select * from r ) select * from (r align r r2 on r.a=r2.a with (ts,te,ts,te)) ) select * from r w
here ts=4 and a='A';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
----
CTE Scan on r (cost=761.62..879.37 rows=1 width=24) (actual time=0.108..0.108 rows=0 loops=1)
Filter: ((ts = 4) AND (a = 'A'::bpchar))
Rows Removed by Filter: 3
CTE r
-> Subquery Scan on r_2 (cost=628.17..761.62 rows=4710 width=24) (actual time=0.097..0.102 rows=3 loops=1)
CTE r
-> Seq Scan on r r_1 (cost=0.00..25.70 rows=1570 width=24) (actual time=0.007..0.008 rows=3 loops=1)
-> Adjustment(for ALIGN) (cost=602.47..688.82 rows=4710 width=40) (actual time=0.096..0.100 rows=3 loops=1)
-> Sort (cost=602.47..606.40 rows=1570 width=40) (actual time=0.091..0.092 rows=3 loops=1)
Sort Key: (row_number() OVER (?)), (GREATEST(r_3.ts, r2.ts)), (LEAST(r_3.te, r2.te))
Sort Method: quicksort Memory: 25kB
-> Merge Left Join (cost=264.80..519.13 rows=1570 width=40) (actual time=0.069..0.076 rows=3 loops=1)
Merge Cond: (r_3.a = r2.a)
Join Filter: ((r_3.ts < r2.te) AND (r_3.te > r2.ts))
-> Sort (cost=150.06..153.99 rows=1570 width=32) (actual time=0.042..0.042 rows=3 loops=1)
Sort Key: r_3.a
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=0.00..51.03 rows=1570 width=24) (actual time=0.016..0.025 rows=3 loops=1)
-> CTE Scan on r r_3 (cost=0.00..31.40 rows=1570 width=24) (actual time=0.008..0.010 rows=3 loops
=1)
-> Sort (cost=114.74..118.66 rows=1570 width=16) (actual time=0.018..0.019 rows=3 loops=1)
Sort Key: r2.a
Sort Method: quicksort Memory: 25kB
-> CTE Scan on r r2 (cost=0.00..31.40 rows=1570 width=16) (actual time=0.001..0.003 rows=3 loops=1)
Planning time: 0.271 ms
Execution time: 0.217 ms
(25 rows)