set enable_nestloop=off did the trick. Execution time when down to seconds
per query.
Thanks very much for your help.
On Wed, Nov 4, 2020 at 4:16 PM Tom Lane wrote:
> Eric Raskin writes:
> > So, things get even weirder. When I execute each individual select
> > statement I am generating fro
So, things get even weirder. When I execute each individual select
statement I am generating from a psql prompt, they all finish very
quickly.
If I execute them inside a pl/pgsql block, the second one hangs.
Is there something about execution inside a pl/pgsql block that is
different from the p
OK -- got it. Thanks very much for your help. I'll see what I can do to
denormalize the case statements into actual columns to support the queries.
On Wed, Nov 4, 2020 at 2:23 PM Tom Lane wrote:
> Eric Raskin writes:
> > And, of course, your explanation that inserts will not be parallelized
>
OK - I see. And to add insult to injury, I tried creating a temporary
table to store the intermediate results. Then I was going to just do an
insert... select... to insert the rows. That would de-couple the
nextval() from the query.
Strangely, the first query I tried it on worked great. But,
And, to follow up on your question, the plan shape DOES change when I
add/remove the nextval() on a plain explain.
Without nextval(): https://explain.depesz.com/s/SCdY
With nextval(): https://explain.depesz.com/s/oLPn
On Wed, Nov 4, 2020 at 1:22 PM Eric Raskin wrote:
> Thanks for the repl
Thanks for the reply. I see that the explain.depesz.com did not show you
the query. My apologies:
select unnest(array[273941676,273941677,273941678,273941679,273941680])
countrow_id,
disporder, fmtdate, typecode,
unnest(array[count_273941676,count_273941677,count_273941678,count_273941
SORRY! Here's a link that should show the plan:
https://explain.depesz.com/s/SCdY
--
Eric Raskin writes:
> So, things get even weirder. When I execute each individual select
> statement I am generating from a psql prompt, they all finish very
> quickly.
> If I execute them inside a pl/pgsql block, the second one hangs.
> Is there something about execution inside a pl/pgsql block
On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin wrote:
> OK - I see. And to add insult to injury, I tried creating a temporary
> table to store the intermediate results. Then I was going to just do an
> insert... select... to insert the rows. That would de-couple the
> nextval() from the query.
>
... btw, it occurs to me that at least as a stopgap,
"set enable_nestloop = off" would be worth trying.
The killer problem with rowcount-1 estimates is that they
encourage the planner to use nestloops when it shouldn't.
regards, tom lane
Eric Raskin writes:
> And, of course, your explanation that inserts will not be parallelized must
> be the reason. I will certainly re-vacuum the tables. I wonder why
> auto-vacuum didn't collect better stats. vacuum analyze is all I
> need, right?
Plain ANALYZE is enough to collect stats; b
Eric Raskin writes:
> And, to follow up on your question, the plan shape DOES change when I
> add/remove the nextval() on a plain explain.
> Without nextval(): https://explain.depesz.com/s/SCdY
> With nextval(): https://explain.depesz.com/s/oLPn
Ah, there's your problem, I think: the plan witho
Eric Raskin writes:
> I have a strange situation where a base query completes in about 30 seconds
> but if I add a nextval() call to the select it never completes. There are
> other processes running that are accessing the same sequence, but I thought
> that concurrency was not an issue for seque
13 matches
Mail list logo
