On Tue, Apr 14, 2020 at 01:16:33PM -0400, James Coleman wrote:
On Sun, Apr 12, 2020 at 8:09 PM Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
On Sun, Apr 12, 2020 at 12:44:45AM +0200, Tomas Vondra wrote:
>Hi,
>
>I've looked into this a bit, and at first I thought that maybe the
>issue is in how cost_incremental_sort picks the EC members. It simply
>does this:
>
> EquivalenceMember *member = (EquivalenceMember *)
> linitial(key->pk_eclass->ec_members);
>
>so I was speculating that maybe there are multiple EC members and the
>one we need is not the first one. That would have been easy to fix.
>
>But that doesn't seem to be the case - in this example the EC ony has a
>single EC member anyway.
>
> (gdb) p key->pk_eclass->ec_members
> $14 = (List *) 0x12eb958
> (gdb) p *key->pk_eclass->ec_members
> $15 = {type = T_List, length = 1, max_length = 5, elements = 0x12eb970,
initial_elements = 0x12eb970}
>
>and the member is a Var with varno=0 (with a RelabelType on top, but
>that's irrelevant).
>
> (gdb) p *(Var*)((RelabelType*)member->em_expr)->arg
> $12 = {xpr = {type = T_Var}, varno = 0, varattno = 1, vartype = 12445,
vartypmod = -1, varcollid = 950, varlevelsup = 0, varnosyn = 0, varattnosyn = 1,
location = -1}
>
>which then triggers the assert in find_base_rel. When looking for other
>places calling estimate_num_groups I found this in prepunion.c:
>
> * XXX you don't really want to know about this: we do the estimation
> * using the subquery's original targetlist expressions, not the
> * subroot->processed_tlist which might seem more appropriate. The
> * reason is that if the subquery is itself a setop, it may return a
> * processed_tlist containing "varno 0" Vars generated by
> * generate_append_tlist, and those would confuse estimate_num_groups
> * mightily. We ought to get rid of the "varno 0" hack, but that
> * requires a redesign of the parsetree representation of setops, so
> * that there can be an RTE corresponding to each setop's output.
>
>which seems pretty similar to the issue at hand, because the subpath is
>T_UpperUniquePath (not sure if that passes as setop, but the symptoms
>match nicely).
>
>Not sure what to do about it in cost_incremental_sort, though :-(
>
I've been messing with this the whole day, without much progress :-(
I'm 99.9999% sure it's the same issue described by the quoted comment,
because the plan looks like this:
Nested Loop Left Join
-> Sample Scan on pg_namespace
Sampling: system ('7.2'::real)
-> Incremental Sort
Sort Key: ...
Presorted Key: ...
-> Unique
-> Sort
Sort Key: ...
-> Append
-> Nested Loop
...
-> Nested Loop
...
so yeah, the plan does have set operations, and generate_append_tlist
does generate Vars with varno == 0, causing this issue.
This is a bit of an oddly shaped plan anyway, right? In an ideal world
the sort for the unique would have knowledge about what would be
useful for the parent node, and we wouldn't need the incremental sort
at all.
Well, yeah. The problem is the Unique simply compares the columns in the
order it sees them, and it does not match the column order desired by
incremental sort. But we don't push down this information at all :-(
In fact, there may be other reasons to reorder the comparisons, e.g.
when the cost is different for different columns. There was a patch by
Teodor IIRC correctly doing exactly that.
I'm not sure that that kind of thing is really a new problem, though,
and it might not even be entirely possible to fix directly by trying
to push down knowledge about useful sort keys to whatever created that
sort path; it might only be fixable by having the incremental sort (or
even regular sort) path creation know to "subsume" a sort underneath
it.
Anyway, I think that's a bit off topic, but it stood out to me.
It's not a new problem. It's an optimization we don't have.
But I'm not entirely sure what to do about it in cost_incremental_sort.
The comment (introduced by 89deca582a in 2017) suggests a proper fix
would require redesigning the parsetree representation of setops, and
it's a bit too late for that.
So I wonder what a possible solution might look like. I was hoping we
might grab the original target list and use that, similarly to
recurse_set_operations, but I'm not sure how/where to get it.
This is also not an area I'm familiar with. Reading through the
prepunion.c code alongside cost_incremental_sort, it seems that we
don't have access to the same level of information as the prepunion
code (i.e., we're only looking at the result of the union, not the
components of it), and trying descend down into it seems even more
gross, so, see below...
Yeah. And I'm not even sure having that information would allow good
estimates e.g. for UNIONs of multiple relations etc.
Another option is to use something as simple as checking for Vars with
varno==0 in cost_incremental_sort() and ignoring them somehow. We could
simply use some arbitrary estimate - by assuming the rows are unique or
something like that. Yes, I agree it's pretty ugly and I'd much rather
find a way to generate something sensible, but I'm not even sure we can
generate good estimate when doing UNION of data from different relations
and so on. The attached (ugly) patch does this.
...therefore I think this is worth proceeding with.
OK, then the question is what estimate to use in this case. Should we
assume 1 group or uniqueness? I'd assume a single group produces costs
slightly above regular sort, right?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
