Re: Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:58 PM, Matt Magoffin <postgresql.org@msqr.us> wrote:

I wonder if this is the problem, or part of it.  This part of the
explain analyze on down, there's 1.4M rows, when the planner seems to
expect the number of rows to be chopped down quite a bit more when it
goes from the bitmap index scan to the bitmap heap scan.  I'm
wondering if you're hitting one of those issues where pgsql thinks it
can fit some operation into work_mem and it can't and the allocation
fails.  Just a guess, I'd run that by someone else before I took it as
fact.

>                                             ->  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)