Never ending query when adding subquery to from-clause

Question

For some reason the following query never ends. However, if I remove the last subquery in the from clause, it runs in 500ms

Any ideas as to why?

select new.app_id, 'support' as domain, 'summary' as type, 90 as interval, json_build_object( 'new', count(new), 'closed', count(closed), ) as data from ( SELECT * from conversations c WHERE c.inserted_at::date > (current_date - (90 || ' days')::interval)::date ) as new, ( SELECT * from conversations c WHERE c.inserted_at::date > (current_date - (90 || ' days')::interval)::date ) as closed group by new.app_id

In select you use json_build_object require that a lot of cpu time. I suggest check count rows with last subquery and without subquery, possible tha with subquery query returning too much rows — Piotr Rogowski
– Piotr Rogowski, Commented Aug 19, 2016 at 18:11
I can't see where you joined the tables. You just placed them, but without a join between them. How do new and closed relate to each other? Perhaps we could help you more if you say what you want and show us some data examples. — Christian
– Christian, Commented Aug 19, 2016 at 19:20
Lots of questions here... you have two subqueries that are identical; you could just make them one with a "with" clause. That said, nothing in either warrants a subquery -- maybe just invoke them directly in your main query? Peeling back the onion, what are you trying to do? If you join the table on itself, using the ID, presumably you will get the exact same data, multiplied by the number of rows. Do you have sample input and expected output? — Hambone
– Hambone, Commented Aug 20, 2016 at 1:24

Zi0n1 · Accepted Answer · 2016-08-19 18:13:07Z

You are not joining your two tables together which is probably causing it to run for an extremely long time. It is basically doing every combo between both tables with the way you are currently running it.

You need to join the two subquerys in the from with a JOIN ON of some sort to save yourself the time.

Potentially join on new.App_id = closed.app_id?

Collectives™ on Stack Overflow

Never ending query when adding subquery to from-clause

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related