Re: Out of Memory errors are frustrating as heck! - Mailing list pgsql-performance

On Tue, Apr 23, 2019 at 04:37:50PM -0400, Gunther wrote:
> On 4/21/2019 23:09, Tomas Vondra wrote:
> >What I think might work better is the attached v2 of the patch, with a
> Thanks for this, and I am trying this now.
...
> Aaaaaand, it's a winner!
> 
> Unique (cost=5551524.36..5554207.33 rows=34619 width=1197) (actual time=6150303.060..6895451.210 rows=435274
loops=1)
> -> Sort (cost=5551524.36..5551610.91 rows=34619 width=1197) (actual time=6150303.058..6801372.192 rows=113478386
loops=1)
 
> Sort Method: external merge Disk: 40726720kB
> 
> For the first time this query has succeeded now. Memory was bounded. The
> time of nearly hours is crazy, but things sometimes take that long

It wrote 40GB tempfiles - perhaps you can increase work_mem now to improve the
query time.

We didn't address it yet, but your issue was partially caused by a misestimate.
It's almost certainly because these conditions are correlated, or maybe
redundant.

> Merge Cond: (((documentinformationsubject.documentinternalid)::text =
(documentinformationsubject_1.documentinternalid)::text)AND ((documentinformationsubject.documentid)::text =
(documentinformationsubject_1.documentid)::text)AND ((documentinformationsubject.actinternalid)::text =
(documentinformationsubject_1.actinternalid)::text))

If they're completely redundant and you can get the same result after dropping
one or two of those conditions, then you should.

Alternately, if they're correlated but not redundant, you can use PG10
"dependency" statistics (CREATE STATISTICS) on the correlated columns (and
ANALYZE).

On Tue, Apr 16, 2019 at 10:24:53PM -0400, Gunther wrote:
>  Hash Right Join  (cost=4203858.53..5475530.71 rows=34619 width=4) (actual time=309603.384..459480.863 rows=113478386
loops=1)
...
>          ->  Hash  (cost=1310249.63..1310249.63 rows=13 width=111) (actual time=51077.049..51077.049 rows=236
loops=1)
...
>    ->  Hash  (cost=2861845.87..2861845.87 rows=34619 width=74) (actual time=199792.446..199792.446 rows=113478127
loops=1)
>          Buckets: 65536 (originally 65536)  Batches: 131072 (originally 2)  Memory Usage: 189207kB
>          ->  Gather Merge  (cost=2845073.40..2861845.87 rows=34619 width=74) (actual time=107620.262..156256.432
rows=113478127loops=1)
 
>                Workers Planned: 2
>                Workers Launched: 2
>                ->  Merge Left Join  (cost=2844073.37..2856849.96 rows=14425 width=74) (actual
time=107570.719..126113.792rows=37826042 loops=3)
 
>                      Merge Cond: (((documentinformationsubject.documentinternalid)::text =
(documentinformationsubject_1.documentinternalid)::text)AND ((documentinformationsubject.documentid)::text =
(documentinformationsubject_1.documentid)::text)AND ((documentinformationsubject.actinternalid)::text =
(documentinformationsubject_1.actinternalid)::text))
>                      ->  Sort  (cost=1295969.26..1296005.32 rows=14425 width=111) (actual time=57700.723..58134.751
rows=231207loops=3)
 
>                            Sort Key: documentinformationsubject.documentinternalid,
documentinformationsubject.documentid,documentinformationsubject.actinternalid
 
>                            Sort Method: external merge  Disk: 26936kB
>                            Worker 0:  Sort Method: external merge  Disk: 27152kB
>                            Worker 1:  Sort Method: external merge  Disk: 28248kB
>                            ->  Parallel Seq Scan on documentinformationsubject  (cost=0.00..1294972.76 rows=14425
width=111)(actual time=24866.656..57424.420 rows=231207 loops=3)
 
>                                  Filter: (((participationtypecode)::text = ANY ('{PPRF,PRF}'::text[])) AND
((classcode)::text= 'ACT'::text) AND ((moodcode)::text = 'DEF'::text) AND ((code_codesystem)::text =
'2.16.840.1.113883.3.26.1.1'::text))
>                                  Rows Removed by Filter: 2584355
>                      ->  Materialize  (cost=1548104.12..1553157.04 rows=1010585 width=111) (actual
time=49869.984..54191.701rows=38060250 loops=3)
 
>                            ->  Sort  (cost=1548104.12..1550630.58 rows=1010585 width=111) (actual
time=49869.980..50832.205rows=1031106 loops=3)
 
>                                  Sort Key: documentinformationsubject_1.documentinternalid,
documentinformationsubject_1.documentid,documentinformationsubject_1.actinternalid
 
>                                  Sort Method: external merge  Disk: 122192kB
>                                  Worker 0:  Sort Method: external merge  Disk: 122192kB
>                                  Worker 1:  Sort Method: external merge  Disk: 122192kB
>                                  ->  Seq Scan on documentinformationsubject documentinformationsubject_1
(cost=0.00..1329868.64rows=1010585 width=111) (actual time=20366.166..47751.267 rows=1031106 loops=3)
 
>                                        Filter: ((participationtypecode)::text = 'PRD'::text)
>                                        Rows Removed by Filter: 7415579