Re: [HACKERS] VIEWS, DISTINCT and COUNT

wieck(at)debis(dot)com (Jan Wieck) writes:
> All these DISTINCT, AGGREGATE etc. problems on views are
> based on the fact, that the planner still requires that the
> rewriters output is equivalent to a regular, allowed query.

Right, and there's no good reason for that.

> I would like to be able to place a complete querytree (just
> an entire SELECT's Query node) into a range table entry.

I've been saying for some time that the parser ought to emit something
close to a plan-tree representation --- not committing to a particular
query implementation method, of course, but nonetheless a tree of
query nodes. The planner wouldn't find that any harder to work on
than what it gets now. The executor might need some work, but
probably not much.

> Unfortunately my knowledge in the planner is very limited, so
> I would need help to go for it. Who has that knowledge?

I know enough to be dangerous, and so does Bruce. Do you think there
is time to attack this for 7.0, or should we leave well enough alone
for now?

> Let's have a view defined as

> CREATE VIEW v1 AS SELECT a, count(*) AS n FROM t1 GROUP BY a;

> The plan for such a query would be a

> Aggregate
> -> Group
> -> Sort
> -> Seq Scan on t1

Not necessarily --- the aggregate and group nodes must be there, but
we don't want to commit to seqscan&sort vs. indexscan sooner than we
have to. I think what's needed here is some notion of an abstract
plan tree. The trick is to pick the right level of abstraction.
Maybe "Aggregate -> Group -> OrderedTupleSource" is the way to think
about it.

But your end point is valid: we want to be able to make a structure
like that be an input to a higher-level plan tree. This is also
necessary for subselect in FROM clause, isn't it?

> Again, who knows enough about the planner to be able to do
> this kind of stuff?

I could take it on, but I have a lot of other stuff I want to do for
7.0. Is this more important than fixing fmgr or improving the
planner's selectivity estimates? I dunno...

regards, tom lane