Thread: Re: Backend-internal SPI operations
Peter Eisentraut wrote: > Jan Wieck writes: > > > And it's time to make more use of the relkind attribute. For > > 7.2, when we want to have tuple-set returns for functions, we > > might want to have structures as well > > After the fabled query-tree redesign, couldn't you implement views simply > like this: > > SELECT * FROM my_view; > > becomes > > SELECT * FROM (view_definition); > Hmmm, don't know what you mean with that. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Peter Eisentraut <peter_e@gmx.net> writes:
> then it becomes
> SELECT * FROM (SELECT a, b, c FROM my_table);
> which would presumably be possible with the new query-tree.
Right, that's exactly how we've been planning to fix the problems
with grouped views and so forth.
I am beginning to think that this may have to happen for 7.1, else
view inside ISO-style JOIN constructs aren't going to work right.
Further news as it happens...
regards, tom lane
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > then it becomes > > SELECT * FROM (SELECT a, b, c FROM my_table); > > which would presumably be possible with the new query-tree. > > Right, that's exactly how we've been planning to fix the problems > with grouped views and so forth. > > I am beginning to think that this may have to happen for 7.1, else > view inside ISO-style JOIN constructs aren't going to work right. > Further news as it happens... You really want to start on that NOW? I suggest let's get what we have out of the door now and don't work under pressure on these complex things. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Peter Eisentraut wrote:
> Jan Wieck writes:
>
> > Hmmm, don't know what you mean with that.
>
> If I define a view
>
> CREATE my_view AS SELECT a, b, c FROM my_table;
>
> and then do
>
> SELECT * FROM my_view;
>
> then it becomes
>
> SELECT * FROM (SELECT a, b, c FROM my_table);
>
> which would presumably be possible with the new query-tree.
Hmm - too simple - real life is harder. So to what do you expand the query
SELECT a, c, d FROM my_view, other_table WHERE my_view.a = other_table.a AND other_table.x =
'foo';
And then have a little more complex "my_view", maybe a join with it's own WHERE clause.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck <janwieck@Yahoo.com> writes:
> I suggest let's get what we have out of the door now and
> don't work under pressure on these complex things.
Pressure? I've got a month, I thought. Should be plenty of time.
regards, tom lane
Jan Wieck <janwieck@Yahoo.com> writes:
> Hmm - too simple - real life is harder. So to what do you
> expand the query
> SELECT a, c, d FROM my_view, other_table
> WHERE my_view.a = other_table.a
> AND other_table.x = 'foo';
SELECT a, c, d FROM (SELECT a, b, c FROM my_table) AS my_view, other_table WHERE my_view.a =
other_table.a AND other_table.x = 'foo';
I'm still not detecting a problem here ... if selecting from a view
*doesn't* act exactly like a sub-SELECT, it'd be broken IMHO.
We're not that far away from being able to do this, and it looks more
attractive to work on that than to hack the rewriter into an even
greater state of unintelligibility ...
regards, tom lane
Tom Lane wrote: > Jan Wieck <janwieck@Yahoo.com> writes: > > Hmm - too simple - real life is harder. So to what do you > > expand the query > > > SELECT a, c, d FROM my_view, other_table > > WHERE my_view.a = other_table.a > > AND other_table.x = 'foo'; > > SELECT a, c, d > FROM (SELECT a, b, c FROM my_table) AS my_view, other_table > WHERE my_view.a = other_table.a > AND other_table.x = 'foo'; > > I'm still not detecting a problem here ... if selecting from a view > *doesn't* act exactly like a sub-SELECT, it'd be broken IMHO. I do. The qualification does not restrict the subselect in any way. So it'll be a sequential scan - no? Imagine my_table has 10,000,000 rows and other_table is small. With an index on my_table.a and the rewritingwe do today there's a good chance to end up with index lookups in my_table for all the other_table matchesof x = 'foo'. Of course, after all the view must behave like a subselect. But please only logical - not physical! So the hard part of the NEW rewriter will be to detect which qualifications can be moved/duplicated down into which subselects (tuple sources) to restrict scans. > We're not that far away from being able to do this, and it looks more > attractive to work on that than to hack the rewriter into an even > greater state of unintelligibility ... Then again, let's get 7.1 out as is and do the full querytree redesign for 7.2. It looks easy, but I fear it's moreor less like an iceberg. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <janwieck@yahoo.com> writes:
> So the hard part of the NEW rewriter will be to detect which
> qualifications can be moved/duplicated down into which
> subselects (tuple sources) to restrict scans.
Actually, what I was envisioning was pulling the subselect's guts *up*
into the main query (collapsing out the sub-Query node) if the sub-Query
is simple enough --- that is, no grouping/sorting/aggregates/etc. The
nice thing about that is we can start with a very simple method that
only deals with easy cases. The hard cases will still *work*.
I consider that an improvement over the current situation, where even
simple cases are nightmarishly difficult to implement (as you well know)
and the hard cases don't work. Worst case is that some
intermediate-complexity examples might lose performance for a while
until we build up a smart subquery-merging algorithm, but that seems
a price worth paying.
> Then again, let's get 7.1 out as is
Has the release schedule moved up without my knowing about it?
I don't feel any urgent need to freeze development now...
> and do the full querytree
> redesign for 7.2. It looks easy, but I fear it's more or less
> like an iceberg.
The original reason for this effort was to do a trial balloon that would
give us more knowledge about how to do it right for 7.2. The more I get
into it, the more I realize what a good idea that was. I'm not sure how
much of what I'm doing now will be completely discarded in the 7.2
cycle, but I do know that I understand the issues a lot better than
I did a week ago...
regards, tom lane