[My machine oopsed sending this the first time. I don't think it got out but
if it did I apologize for the duplicate]
In this plan the optimizer is reluctant to push the filter down into the view.
I think it should be safe to push it inside unique nodes as long as the where
clause uses only expressions in the distinctness clause.
I'm assuming you're interesting in seeing such points since they probably
represent small changes that can be made to the heuristics that would have a
big impact. This one in particular I'm finding frustrating since it would bite
me if I tried to abstract away a lot of complexity by creating a view. If I'm
sending too many of these things that's ok, I can calm down :)
slo=> explain select * from (select distinct on (x) x,y from ttt order by 1,2) as x where x = 1;
QUERY PLAN
---------------------------------------------------------------------
Subquery Scan x (cost=0.01..0.02 rows=1 width=8)
Filter: (x = 1::double precision)
-> Unique (cost=0.01..0.02 rows=1 width=8)
-> Sort (cost=0.01..0.02 rows=1 width=8)
Sort Key: x, y
-> Seq Scan on ttt (cost=0.00..0.00 rows=1 width=8)
This seems to be done already for other aggregates:
slo=> explain select * from (select n,first(m) from (select n,m from m order by n,m) as y group by n) as x where n=1;
QUERY PLAN
-------------------------------------------------------------------------------------
Subquery Scan x (cost=1.27..1.28 rows=1 width=8)
-> Aggregate (cost=1.27..1.28 rows=1 width=8)
-> Group (cost=1.27..1.28 rows=1 width=8)
-> Sort (cost=1.27..1.28 rows=1 width=8)
Sort Key: n
-> Subquery Scan y (cost=1.26..1.26 rows=1 width=8)
-> Sort (cost=1.26..1.26 rows=1 width=8)
Sort Key: n, m
-> Seq Scan on m (cost=0.00..1.25 rows=1 width=8)
Filter: (n = 1)
--
greg
