Re: AWS forcing PG upgrade from v9.6 a disaster

[Jan Wieck]

On Fri, May 28, 2021, 22:41 Andrew Dunstan  wrote:

>
> On 5/28/21 10:27 PM, Jan Wieck wrote:
> >
> >
> > On Fri, May 28, 2021, 17:15 Andrew Dunstan  > > wrote:
> >
> >
> >
> >
> > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
> > assume you would know better than him or me what it actually does
> > do :-)
> >
> >
> > Since I am not working at AWS I can't tell for sure. ;)
>
>
> Apologies, my mistake then.
>

No need to apologize, you were correct two months ago.


Best Regards, Jan



>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>

Re: AWS forcing PG upgrade from v9.6 a disaster

[Dean Gibson (DB Administrator)]

On 2021-05-29 09:25, Adrian Klaver wrote:

On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:

Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your 
first post and post them here:


https://explain.depesz.com/

Other information:
1) A diff of your configuration settings between 9.6 and 13.2.

2) Are you running on the same AWS instance type for the two versions 
of Postgres?


It is not necessary to repeat the table/view definitions as they are 
available in the first post.


Done.

1.There's probably about a hundred, but almost all are differences in 
the default values.  The most interesting (from my point of view) is my 
setting work_mem in 8000 on v9.6, & 16000 (after 8000 didn't help) on 
v13.  Doing a compare right now between the DEFAULT parameters for 9.6 & 
13, RDS reports 93 differences in the default parameters between the two.


2. For v13, I moved from db.t2.micro to db.t3.micro, because RDS 
required that for v13.  However, for the v10, 11, 12 upgrades, I kept 
db.t2.micro.


Meanwhile, I've been doing some checking.  If I remove "CAST( 
license_status AS CHAR ) = 'A'", the problem disappears.  Changing the 
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the 
problem, but there is an extra row where license_status is NULL, due to 
the RIGHT JOIN.  Currently trying to figure that out (why did the CAST 
... match 'A', if it is null?)...

Re: AWS forcing PG upgrade from v9.6 a disaster

[Dean Gibson (DB Administrator)]

On 2021-05-28 22:24, Alexey M Boltenkov wrote:

On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: [deleted]

BTW what is the planner reason to not use index in v13.2? Is index in 
corrupted state? Have you try to reindex index 
"FccLookup"."_LicStatus_pkey" ?


1.5M of seqscan's are looking really bad.

 SubPlan 2
   ->  Limit  (cost=0.15..8.17 rows=1 width=32) 
(actual time=0.006..0.007 rows=1 loops=55)
 -> *Index Scan using "_LicStatus_pkey" on 
"_LicStatus"*  (cost=0.15..8.17 rows=1 width=32) (actual 
time=0.005..0.005 rows=1 loops=55)
   Index Cond: ("_HD".license_status = 
status_id)



SubPlan 2
   ->  Limit  (cost=0.00..1.07 rows=1 width=13) 
(actual time=0.001..0.001 rows=1 loops=1487153)
 -> *Seq Scan on "_LicStatus"*  
(cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1 
loops=1487153)
   Filter: ("_HD".license_status = 
status_id)

   Rows Removed by Filter: 1



Doing your REINDEX didn't help.  Now in the process of reindexing the 
entire database.  When that's done, I'll let you know if there is any 
improvement.

Re: AWS forcing PG upgrade from v9.6 a disaster

[Andrew Dunstan]

On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>
>
> Meanwhile, I've been doing some checking.  If I remove "CAST(
> license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
> JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
> problem, but there is an extra row where license_status is NULL, due
> to the RIGHT JOIN.  Currently trying to figure that out (why did the
> CAST ... match 'A', if it is null?)...


Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: AWS forcing PG upgrade from v9.6 a disaster

[Dean Gibson (DB Administrator)]

I tried 500, to no avail.  Since each change involves a delay as RDS 
readjusts, I'm going down a different path at the moment.


On 2021-05-29 03:40, Lionel Bouton wrote:

Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
The original VACUUM FULL ANALYZE ran in 10 hours.  The plain ANALYZE 
ran in 88 seconds.


One possibility is that your data has a distribution that defeats the 
ANALYZE sampling strategy.


If that is the case you can force ANALYZE to do a better job by 
increasing the default_statistics_target value (100 by default) and 
reload the configuration. This will sample more data from your table 
which should help the planner find out what the value distribution 
looks like for a column and why using an index for conditions 
involving it is a better solution.
The last time I had to use this setting to solve this kind of problem 
I ended with :


default_statistics_target = 500

But obviously the value suited to your case could be different (I'd 
increase it until the planner uses the correct index). Note that 
increasing it increases the costs of maintaining statistics (so you 
don't want to increase this by several orders of magnitude blindly) 
but the default value seems fairly conservative to me.


For reference and more fine-tuned settings using per table statistics 
configuration and multi-column statistics for complex situations, see :

- https://www.postgresql.org/docs/13/runtime-config-query.html
- https://www.postgresql.org/docs/13/planner-stats.html

--
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/