The plans look good for the carrydown execution, so I have to conclude that the long-running queries are due to other considerations -- perhaps concurrent vacuuming, perhaps other queries pounding the database.

Sharepoint is especially hard on the carrydown table -- it gets huge.

Karl


On Wed, Nov 29, 2017 at 4:46 PM, Markus Schuch <markus.schuch@deutschebahn.com> wrote:

Hi,

 

since nobody responded, we started to experiment.

 

Setup:

AWS RDS Postgres 9.6.3

db.m4.xlarge (4 cores, 16 GB RAM)

provisioned iops 4000

 

Indexing Performance for most jobs is pretty good, except a for a bigger sharepoint crawl (~200.000 docs).

 

We are seeing a lot of long running queries for the tables carrydown here.

 

Logfile:

https://gist.githubusercontent.com/schuch/b553dc3f825e0c640a52ce3f530ec3e0/raw/34e493ef520d2272b25f7d9d8acc3f8417550c17/gistfile1.txt

 

Settings:

https://gist.githubusercontent.com/schuch/44fcbb312df37ab89a910024a56ff835/raw/eba6ff9bc82026f181d5687584e9dd99cdffb242/postgresql%2520RDS%2520settings

(due to this discussion we left autovac on: https://www.mail-archive.com/user@manifoldcf.apache.org/msg03512.html)

 

CPU Usage of the Database is between 15% and 35%

 

Are we hitting bad auto optimiziations of the newer postgresql version?

 

Many thanks in advance,

Markus