Re: Out of memory on SELECT in 8.3.5 - Mailing list pgsql-general

> I'd do both.  But only after I'd reduced work_mem.  Given that
> reducing work_mem removed the problem, it looks to me like pgsql is
> requesting several large blocks of ram, then only using a small port
> of them.  But overcommit set to 2 means that the OS will not allow an
> overcommit of memory to these allocations, the allocations fail, and
> you get your error.

OK, I've re-configured work_mem, and set vm.overcommit_ratio to 80.
Without restarting Postgres, I was now able to run that big query posted
earlier in this thread that failed... so the overcommit_ratio adjustment
helped there. The EXPLAIN ANALYZE for that I've included below, where I
can see it did use in-memory sorting for some of the sorts.

                                                                                                        QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2297842.92..2297943.76 rows=40336 width=234) (actual
time=370440.041..370483.133 rows=49317 loops=1)
   Sort Key: s.sale_date, s.vin
   Sort Method:  quicksort  Memory: 9357kB
   ->  Hash Left Join  (cost=1450365.72..2294757.26 rows=40336 width=234)
(actual time=293212.004..369857.956 rows=49317 loops=1)
         Hash Cond: (ml.lead_id = phone.lead_id)
         ->  Hash Left Join  (cost=1341348.27..2140418.89 rows=40336
width=219) (actual time=286374.489..362880.702 rows=49317
loops=1)
               Hash Cond: (ml.lead_id = email.lead_id)
               ->  Nested Loop Left Join  (cost=1237270.73..2000634.35
rows=40336 width=204) (actual time=278652.051..355022.014
rows=49317 loops=1)
                     ->  Nested Loop Left Join
(cost=1237270.73..1807035.53 rows=40336 width=141)
(actual time=278635.414..323774.871 rows=49317
loops=1)
                           ->  Nested Loop Left Join
(cost=1237270.73..1587513.22 rows=40336
width=126) (actual time=278632.427..317952.620
rows=49317 loops=1)
                                 ->  Nested Loop Left Join
(cost=1237270.73..1414103.19 rows=40336
width=118) (actual
time=278606.034..297951.038 rows=49317
loops=1)
                                       ->  Merge Left Join
(cost=1237270.73..1238315.78
rows=40336 width=89) (actual
time=278587.812..279498.796
rows=49317 loops=1)
                                             Merge Cond: (ml.lead_id =
lrdm.lead_id)
                                             ->  Sort
(cost=46384.08..46484.92
rows=40336 width=78) (actual
time=6200.810..6240.526
rows=49317 loops=1)
                                                   Sort Key: ml.lead_id
                                                   Sort Method:  quicksort
 Memory: 8472kB
                                                   ->  Hash Left Join
(cost=9430.33..43298.42
rows=40336 width=78)
(actual
time=1079.869..6084.010
rows=49317 loops=1)
                                                         Hash Cond:
((s.dealer_code)::text
=
(d.dealer_code)::text)
                                                         ->  Hash Left
Join
(cost=9094.04..42407.50
rows=40336
width=60) (actual
time=1074.170..5947.646
rows=49317
loops=1)
                                                               Hash Cond:
(s.id =
ml.sale_id)
                                                               Filter:
(((s.sale_type
=
'd'::bpchar)
AND (NOT
ml.lost_sale))
OR
((s.sale_type
=
'c'::bpchar)
AND
(ml.lead_pos
= 0)) OR
(s.sale_type
=
'0'::bpchar))
                                                               ->  Index
Scan using
mb_sale_sale_date_idx
on mb_sale
s
(cost=0.00..14027.94
rows=43804
width=50)
(actual
time=55.663..4683.901
rows=49152
loops=1)
                                                                     Index
Cond:
((sale_date
>=
'2009-01-01'::date)
AND
(sale_date
<=
'2009-01-31'::date))
                                                               ->  Hash
(cost=5577.35..5577.35
rows=281335
width=26)
(actual
time=1018.108..1018.108
rows=281335
loops=1)
                                                                     ->
Seq
Scan
on
mb_lead
ml
(cost=0.00..5577.35
rows=281335
width=26)
(actual
time=3.451..516.245
rows=281335
loops=1)
                                                         ->  Hash
(cost=321.13..321.13
rows=1213
width=23) (actual
time=5.577..5.577
rows=1213
loops=1)
                                                               ->  Seq
Scan on
dealer d
(cost=0.00..321.13
rows=1213
width=23)
(actual
time=0.056..3.684
rows=1213
loops=1)
                                             ->  Sort
(cost=1190886.66..1191208.43
rows=128709 width=19) (actual
time=270075.460..271851.519
rows=1442527 loops=1)
                                                   Sort Key: lrdm.lead_id
                                                   Sort Method:  external
sort  Disk: 56072kB
                                                   ->  Bitmap Heap Scan on
lead_reporting_meta
lrdm
(cost=118847.85..1179963.28
rows=128709 width=19)
(actual
time=103684.796..261544.708
rows=1462381 loops=1)
                                                         Recheck Cond:
(item_key =
'[ALS:prospectid]TrackingCode'::text)
                                                         Filter: (pos = 1)
                                                         ->  Bitmap Index
Scan on
lead_reporting_meta_item_key_idx

(cost=0.00..118815.67
rows=1476580
width=0) (actual
time=102982.150..102982.150
rows=1484068
loops=1)
                                                               Index Cond:
(item_key =
'[ALS:prospectid]TrackingCode'::text)
                                       ->  Index Scan using
lead_reporting_address_lead_id_idx
on lead_reporting_address address
(cost=0.00..4.35 rows=1 width=37)
(actual time=0.370..0.371 rows=0
loops=49317)
                                             Index Cond: (address.lead_id
= ml.lead_id)
                                 ->  Index Scan using
lead_reporting_street_address_id_idx on
lead_reporting_street address_street
(cost=0.00..4.29 rows=1 width=24) (actual
time=0.402..0.403 rows=0 loops=49317)
                                       Index Cond:
(address_street.address_id =
address.id)
                                       Filter: (address_street.pos = 0)
                           ->  Index Scan using lead_pkey on lead l
(cost=0.00..5.43 rows=1 width=23) (actual
time=0.114..0.115 rows=0 loops=49317)
                                 Index Cond: (l.id = ml.lead_id)
                     ->  Index Scan using lead_reporting_data_pkey on
lead_reporting_data lrd  (cost=0.00..4.79 rows=1
width=71) (actual time=0.630..0.630 rows=0
loops=49317)
                           Index Cond: (lrd.lead_id = ml.lead_id)
               ->  Hash  (cost=85837.99..85837.99 rows=1459164 width=23)
(actual time=7719.918..7719.918 rows=1522674 loops=1)
                     ->  Seq Scan on lead_reporting_list_data email
(cost=0.00..85837.99 rows=1459164 width=23) (actual
time=6.258..5105.843 rows=1522674 loops=1)
                           Filter: ((list_type = 'e'::bpchar) AND (pos = 0))
         ->  Hash  (cost=85837.99..85837.99 rows=1854357 width=23) (actual
time=6834.882..6834.882 rows=1805273 loops=1)
               ->  Seq Scan on lead_reporting_list_data phone
(cost=0.00..85837.99 rows=1854357 width=23) (actual
time=0.027..3917.300 rows=1805273 loops=1)
                     Filter: ((list_type = 'p'::bpchar) AND (pos = 0))
 Total runtime: 370595.083 ms
(51 rows)