Re: Adding nextval() to a select caused hang/very slow execution

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eric Raskin <eraskin(at)paslists(dot)com> 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 <table> is all
> I
> > need, right?
>
> Plain ANALYZE is enough to collect stats; but I doubt that'll improve
> matters for you. The problem is basically that the planner can't do
> anything with a CASE construct, so you end up with default selectivity
> estimates for anything involving a CASE, statistics or no statistics.
> You need to try to reformulate the query with simpler join conditions.
>
> > As a last resort, what about a PL/PGSQL procedure loop on the query
> > result? Since the insert is very few rows relative to the work the
> select
> > has to do, I could just turn the insert.. select.. into a for loop. Then
> > the select could be parallel?
>
> Maybe, but you're still skating on a cliff edge. I think it's pure chance
> that the parallelized query is working acceptably well; next month with
> slightly different conditions, it might not.
>
> regards, tom lane
>

--

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Eric H. Raskin
914-765-0500 x120 or *315-338-4461
(direct)*

Professional Advertising Systems Inc.
fax: 914-765-0500 or *315-338-4461 (direct)*

3 Morgan Drive #310
eraskin(at)paslists(dot)com

Mt Kisco, NY 10549
http://www.paslists.com