JIT taking 30 seconds for a single query is quite unexpected / extreme. I'm sure we'd be interested in more details, so if you could send some details (PostgreSQL version, OS, llvm versions, query and explain plans) on pgsql-performance, that'd be helpful.
As for the estimates - doing analyze is good, but it's possible the estimates are still wrong even with fresh statistics. For example, there might be expressions that are difficult to estimate well. Joins are notoriously difficult to estimate well. Sometimes the ORM generates queries that are difficult to estimate, etc. So it's possible the estimates were off, confusing the planner enough to pick a poor plan. And setting the cost parameters simply confused it "in the opposite direction" enough to pick a better plan. It's impossible to say without looking at the explain analyze. Planner gets improved between versions, so maybe that's why you saw different behavior on different PostgreSQL versions.
What I'd suggest is picking one or two queries that perform particularly poorly, do explain analyze on them (both with the default and modified cost values, and maybe even on different PostgreSQL versions) and send that to pgsql-performance mailing list. If you ping me, I'll take a look.
Quick question on the logistics for this - the query is about 25K, and the explains are about 150K. Do you want these included in-line text within the email, as attachments, zip attached, or ??? (I don't want to spam the list or create problems for anyone, and I don't see anything in either wiki.postgresql.org/wiki/Guide_to_... or wiki.postgresql.org/wiki/Slow_Quer... that seems to address issues with this much information to supply - my apologies if it's there and I've just missed it.)
I do see the reference to explain.depesz.com, I can see where that will help...
Definitely as attachments, not in-line - the clients sometimes wrap / reflow the lines, making it impossible to read (particularly for such long queries / explains). Compressing it won't hurt, of course, although I'm pretty sure it'll be compressed during transfer anyway.
explain.depesz.com is an option too, and I've been recommending that in the past, but that only really addresses the explain plan, not the queries. So just send it as attachements, and the readers can always copy it into explain.depesz.com if they wish.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
JIT taking 30 seconds for a single query is quite unexpected / extreme. I'm sure we'd be interested in more details, so if you could send some details (PostgreSQL version, OS, llvm versions, query and explain plans) on pgsql-performance, that'd be helpful.
As for the estimates - doing analyze is good, but it's possible the estimates are still wrong even with fresh statistics. For example, there might be expressions that are difficult to estimate well. Joins are notoriously difficult to estimate well. Sometimes the ORM generates queries that are difficult to estimate, etc. So it's possible the estimates were off, confusing the planner enough to pick a poor plan. And setting the cost parameters simply confused it "in the opposite direction" enough to pick a better plan. It's impossible to say without looking at the explain analyze. Planner gets improved between versions, so maybe that's why you saw different behavior on different PostgreSQL versions.
What I'd suggest is picking one or two queries that perform particularly poorly, do explain analyze on them (both with the default and modified cost values, and maybe even on different PostgreSQL versions) and send that to pgsql-performance mailing list. If you ping me, I'll take a look.
Quick question on the logistics for this - the query is about 25K, and the explains are about 150K. Do you want these included in-line text within the email, as attachments, zip attached, or ??? (I don't want to spam the list or create problems for anyone, and I don't see anything in either wiki.postgresql.org/wiki/Guide_to_... or wiki.postgresql.org/wiki/Slow_Quer... that seems to address issues with this much information to supply - my apologies if it's there and I've just missed it.)
I do see the reference to explain.depesz.com, I can see where that will help...
Definitely as attachments, not in-line - the clients sometimes wrap / reflow the lines, making it impossible to read (particularly for such long queries / explains). Compressing it won't hurt, of course, although I'm pretty sure it'll be compressed during transfer anyway.
explain.depesz.com is an option too, and I've been recommending that in the past, but that only really addresses the explain plan, not the queries. So just send it as attachements, and the readers can always copy it into explain.depesz.com if they wish.