Query killed with Out of memory - Mailing list pgsql-general

Hello,

With a heavy query, when line number results raise over 600k query hangs with out of memory.

Here is the explain analyze:

CTE Scan on lista  (cost=25066.66..47721.23 rows=3678 width=260)

   CTE lista

     ->  Unique  (cost=24956.32..25066.66 rows=3678 width=512)

           ->  Sort  (cost=24956.32..24965.52 rows=3678 width=512)

                 Sort Key: "*SELECT* 1".id, "*SELECT* 1".data_log, "*SELECT* 1".type_log, "*SELECT* 1".ip, "*SELECT*
1".log_id,"*SELECT* 1".url_dominio, "*SELECT* 1".porta, "*SELECT* 1".action_bind, "*SELECT* 1".action,
('DNS_DENIED/403'

::text), "*SELECT* 1".array_dominio

                 ->  Append  (cost=905.76..24738.50 rows=3678 width=512)

                       ->  Subquery Scan "*SELECT* 1"  (cost=905.76..12423.64 rows=3652 width=512)

                             ->  Hash Anti Join  (cost=905.76..12387.12 rows=3652 width=512)

                                   Hash Cond: (((public.webtraffic_bind.ip)::text = (wb.ip)::text) AND
((public.webtraffic_bind.log_id)::text= (wb.log_id)::text) AND (public.webtraffic_bind.url_dominio = wb.url_dominio)
AND((public.webt 

raffic_bind.porta)::text = (wb.porta)::text))

                                   Join Filter: ((wb.data_log >= public.webtraffic_bind.data_log) AND (wb.data_log <
(public.webtraffic_bind.data_log+ '00:02:00'::interval))) 

                                   ->  Bitmap Heap Scan on webtraffic_bind  (cost=269.23..11638.68 rows=3678 width=512)

                                         Recheck Cond: ((type_log)::text = 'queries'::text)

                                         ->  Bitmap Index Scan on type_log_wbidx  (cost=0.00..268.31 rows=3678 width=0)

                                               Index Cond: ((type_log)::text = 'queries'::text)

                                   ->  Hash  (cost=636.17..636.17 rows=18 width=274)

                                         ->  Bitmap Heap Scan on webtraffic_bind wb  (cost=564.94..636.17 rows=18
width=274)

                                               Recheck Cond: (((type_log)::text = 'security'::text) AND
((action_bind)::text= 'approved'::text)) 

                                               ->  BitmapAnd  (cost=564.94..564.94 rows=18 width=0)

                                                     ->  Bitmap Index Scan on type_log_wbidx  (cost=0.00..268.31
rows=3678width=0) 

                                                           Index Cond: ((type_log)::text = 'security'::text)

                                                     ->  Bitmap Index Scan on action_bind_wbidx  (cost=0.00..296.37
rows=3678width=0) 

                                                           Index Cond: ((action_bind)::text = 'approved'::text)

                       ->  Subquery Scan "*SELECT* 2"  (cost=905.76..12314.86 rows=26 width=512)

                             ->  Hash Semi Join  (cost=905.76..12314.60 rows=26 width=512)

                                   Hash Cond: (((public.webtraffic_bind.ip)::text = (wb.ip)::text) AND
((public.webtraffic_bind.log_id)::text= (wb.log_id)::text) AND (public.webtraffic_bind.url_dominio = wb.url_dominio)
AND((public.webt 

raffic_bind.porta)::text = (wb.porta)::text))

                                   Join Filter: ((wb.data_log >= public.webtraffic_bind.data_log) AND (wb.data_log <
(public.webtraffic_bind.data_log+ '00:02:00'::interval))) 

                                   ->  Bitmap Heap Scan on webtraffic_bind  (cost=269.23..11638.68 rows=3678 width=512)

                                         Recheck Cond: ((type_log)::text = 'queries'::text)

                                         ->  Bitmap Index Scan on type_log_wbidx  (cost=0.00..268.31 rows=3678 width=0)

                                               Index Cond: ((type_log)::text = 'queries'::text)

                                   ->  Hash  (cost=636.17..636.17 rows=18 width=274)

                                         ->  Bitmap Heap Scan on webtraffic_bind wb  (cost=564.94..636.17 rows=18
width=274)

                                               Recheck Cond: (((type_log)::text = 'security'::text) AND
((action_bind)::text= 'approved'::text)) 

                                               ->  BitmapAnd  (cost=564.94..564.94 rows=18 width=0)

                                                     ->  Bitmap Index Scan on type_log_wbidx  (cost=0.00..268.31
rows=3678width=0) 

                                                           Index Cond: ((type_log)::text = 'security'::text)

                                                     ->  Bitmap Index Scan on action_bind_wbidx  (cost=0.00..296.37
rows=3678width=0) 

                                                           Index Cond: ((action_bind)::text = 'approved'::text)

   SubPlan 2

     ->  Index Scan using stpestensioni_domini_idx on stpestensioni_domini  (cost=0.01..12.18 rows=2 width=0)

           Index Cond: ((estensione)::text = ((($1)[(array_length($1, 1) - 1)] || '.'::text) || ($1)[array_length($1,
1)]))

   SubPla
--------

Work_mem is.512mb, shared buffers 3084mb and system Ram 10Gb. Postgres version is 8.4.8 and for some months i cannot
upgrade.

Is there a way to solve the problem?

Thank you, very best regards.
Francesco