Re: Joining on CTE is unusually slow?

On Mon, Aug 4, 2014 at 3:48 PM, Jon Rosebaugh [via PostgreSQL] <
ml-node+s1045698n5813736h40(at)n5(dot)nabble(dot)com> wrote:

> On Mon, Aug 4, 2014, at 06:40 PM, Jon Rosebaugh wrote:
>
> > On Tue, Jul 29, 2014, at 05:38 PM, David G Johnston wrote:
> > > You should at least provide some explain a/o explain analyse results.
> > >
> > > Not to sound pedantic here but you are not JOINing on the CTE, you are
> > > pushing it into WHERE clause via a pair of sub-selects.
> >
> > Fair criticisms. Okay, here we go. Table schemas at the end of the
> > email. My apologies for the length; I didn't want to try trimming out
> > "irrelevant" things that would end up causing actual changes to the
> > question I'm asking. I've run EXPLAIN ANALYZE for all the queries.
>
> Looks like line wrapping might have done a number on the explain output.
> I've put it all in the following pastebin just in case:
>
> https://gist.github.com/inklesspen/7e2577cf6fa9f73bc9c2
>
>
>
​At a very high level your RECURSIVE WITH is estimated to generate 7,291
rows while in reality it only generates 6. By itself it runs quickly (so
its plan is relatively resistant to bad statistics) and when you explicitly
put those 6 IDs into the main query you are OK but as soon as you indicate
that the main query needs to be prepared to accept 7,291 input rows the
system is picking a hash-based plan and that plan is spitting data out to
disk on the order of 1GB > "Sort Method: external merge Disk: 1077000kB "
which is basically a death sentence.

Someone more knowledgeable than myself can probably give better advice but
I have to image the ability for the planner to correctly estimate a WITH
RECURSIVE is hampered by reality. The first work-around that comes to mind
is to move the entire WITH RECURSIVE piece into a function and use "CREATE
FUNCTION ( ... ) ROWS 10" to give the planner more accurate data to work
with. The limitation of this method is obvious so it may be unacceptable
for your use case - but I have no other idea at the moment.

The planner estimate for the CTE will require actual data to explore -
having just a schema will be insufficient. Though the people who know this
system better may be able to provide insight without data if this is a
known limitation situation.

If you must use iteration to develop the input IDs then RECURSIVE CTE or a
procedural function are the only two options I can think of to implement
that portion of the query and so writing a query using just subqueries of
normal relations - which can be optimized better - doesn't appear to be an
option. Depending on your use case you could break the query physically
into two statements - using a temporary table to hold the results of the
first/CTE query - and so the second/main query would not have the same
estimation problem. This avoids having to specify a ROW parameter on a
custom function that may not be close enough to reality in some situations.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Joining-on-CTE-is-unusually-slow-tp5813233p5813740.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.