Re: Joining on CTE is unusually slow?

Jon Rosebaugh wrote
> This takes over eight minutes to run. Is this the expected behavior when
> joining on CTE expressions?
>
> I realize I haven't given the full schema/metadata/explain output as
> explained in the "Slow Query Questions" wiki page

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.

I don't see why you wouldn't apply the result of the CTE to the "FROM
msg_table" in the main query...

SELECT ...
FROM (SELECT * FROM msg_table JOIN downstream_thread USING (id)) AS
notification_reply_message
JOIN ...

Or even just

SELECT ...
FROM downstream_thread
JOIN msg_table USING (id)

Speculation as to your original queries is beyond me without seeing the
explain plans - and possibly even then.

David J.

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