Re: Joining on CTE is unusually slow? - Mailing list pgsql-general
| From | David G Johnston |
|---|---|
| Subject | Re: Joining on CTE is unusually slow? |
| Date | |
| Msg-id | CAKFQuwZ6xvCw9ea8W0OujUOTai8nPZnKCpiKjUf8P4GJi54fYA@mail.gmail.com Whole thread Raw |
| In response to | Re: Joining on CTE is unusually slow? (Jon Rosebaugh <jon@inklesspen.com>) |
| List | pgsql-general |
On Mon, Aug 4, 2014, at 06:40 PM, Jon Rosebaugh wrote:Looks like line wrapping might have done a number on the explain output.
> 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.
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: Re: Joining on CTE is unusually slow?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
pgsql-general by date: