Re: Sudden insert performance degradation - Mailing list pgsql-performance

On 15. Jul 2020, at 03:13, Henrique Montenegro <typoon@gmail.com> wrote:

[...]

```
ssl = off
shared_buffers = 160GB                  # min 128kB
work_mem = 96GB                         # min 64kB
maintenance_work_mem = 12GB             # min 1MB
max_stack_depth = 4MB                   # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
synchronous_commit = off                # synchronization level;
commit_delay = 100000                   # range 0-100000, in microseconds
max_wal_size = 3GB
min_wal_size = 1GB
min_parallel_index_scan_size = 64kB
effective_cache_size = 96GB
log_min_messages = debug1 # values in order of decreasing detail:
log_checkpoints = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_line_prefix = '%m [%p] %q%u@%d '            # special values:
log_lock_waits = on                     # log lock waits >= deadlock_timeout
log_timezone = 'America/New_York'
log_executor_stats = on
datestyle = 'iso, mdy'
```

[...]

  Limit  |  Time (seconds)
---------|------------------
10       | 0.6
100      | 0.6
1000     | 1.3
10000    | 116.9
100000   | 134.8
1000000  | 193.2

Notice the jump in time execution from a 1k limit to a 10k limit. Amount of
data raised 10x and execution time raised 100x.

It seems to me that inserting the data in this case is slow because the time
it takes to identify the duplicate records (which I assume would be done in a
fashion similiar to the queries above) is taking a long time.

I have attached the `explain analyze` output for the 1k and 10k queries to
this email (they are 4k+ lines each, didn't want to make this messager bigger
than it already is).

* exp1k.txt
* exp10k.txt

[...]