Re: Custom Plan node

On Tue, Sep 10, 2013 at 11:45 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>> Fair enough, I think. So the action item for KaiGai is to think of
>> how the planner integration might work.
>>
> Do you think the idea I mentioned at the upthread is worth to investigate
> for more detailed consideration? Or, does it seems to you short-sighted
> thinking to fit this infrastructure with planner?
>
> It categorizes plan node into three: join, scan and other stuff.
> Cost based estimation is almost applied on join and scan, so abstracted
> scan and join may make sense to inform core planner what does this
> custom plan node try to do.
> On the other hand, other stuff, like Agg, is a stuff that must be added
> according to the provided query, even if its cost estimation was not small,
> to perform as the provided query described.
> So, I thought important one is integration of join and scan, but manipulation
> of plan tree for other stuff is sufficient.
>
> How about your opinion?

Well, I don't know that I'm smart enough to predict every sort of
thing that someone might want to do here, unfortunately. This is a
difficult area: there are many possible things someone might want to
do, and as Tom points out, there's a lot of special handling of
particular node types that can make things difficult. And I can't
claim to be an expert in this area.

That having been said, I think the idea of a CustomScan node is
probably worth investigating. I don't know if that would work out
well or poorly, but I think it would be worth some experimentation.
Perhaps you could have a hook that gets called for each baserel, and
can decide whether or not it wishes to inject any additional paths;
and then a CustomScan node that could be used to introduce such paths.
I've been thinking that we really ought to have the ability to
optimize CTID range queries, like SELECT * FROM foo WHERE ctid > 'some
constant'. We have a Tid Scan node, but it only handles equalities,
not inequalities. I suppose this functionality should really be in
core, but since it's not it might make an interesting test for the
infrastructure you're proposing. You may be able to think of
something else you like better; it's just a thought.

I am a little less sanguine about the chances of a CustomJoin node
working out well. I agree that we need something to handle join
pushdown, but it seems to me that might be done by providing a Foreign
Scan path into the joinrel rather than by adding a concept of foreign
joins per se. There are other possible new join types, like the Range
Join that Jeff Davis has mentioned in the past, which might be
interesting. But overall, I can't see us adding very many more join
types, so I'm not totally sure how much extensibility would help us
here. We've added a few scan types over the years (index only scans
in 9.2, and bitmap index scans in 8.1, I think) but all of our
existing join types go back to time immemorial.

And I think that lumping everything else together under "not a scan or
join" has the least promise of all. Is replacing Append really the
same as replacing Sort? I think we'll need to think harder here about
what we're trying to accomplish and how to get there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company