Hi David,
Thanks a lot for your response.
> If there was some way to make a parameterised nested loop more favourable,
> then that might help you.
Setting enable_hashjoin=OFF sped up the execution, but unfortunately we have
not means to inject this into to application ☹
> That would require a
On Wednesday, November 4, 2020, Koteswara Rao Daliparthi
wrote:
> Hi Guys, Sorry for the bold below. I just feel it helps others identify
> my question easily.
>
You stopped following the reporting form too soon. You also need to
provide the “questions about queries” section. Or “slow queries
Hi Guys, Sorry for the bold below. I just feel it helps others identify my
question easily.
A description of what you are trying to achieve and what results you expect.:
A very low cost query running long (clustered and vacuumed and analyzed on join
columns), a very generic query..What can be
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
Hello (TL;DR):
Noob here, so please bear with me. The SQL I'm presenting is part of a
larger PL/PGSQL script that generates generic "counts" from tables in our
database. This is code converted from an Oracle database that we recently
migrated from.
I have a strange situation where a base query
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.
>
On Wed, 4 Nov 2020 at 02:20, Ehrenreich, Sigrid wrote:
>
> -- Statement
> explain SELECT
> count(*)
> FROM
> dim INNER JOIN fact ON (dim.part_key=fact.part_key)
> WHERE dim.part_key >= 110 and dim.part_key <= 160;
>
> Plan shows me, that all partitions are scanned:
> Aggregate (cost=461.00..461.0
... 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
Hi Laurenz,
That trick did it!
Great idea!
I have tested it not only successfully with my little testcase, but with our
real world data and it works there as well.
Thanks a lot for your help!
Regards,
Sigrid
-Original Message-
From: Laurenz Albe
Sent: Tuesday, November 3, 2020 4:45
18 matches
Mail list logo
