Performance problems with 8.1.1 compared to 7.4.7 - Mailing list pgsql-performance

Hello,
    we have a PostgreSQL for datawarehousing. As we heard of so many enhancements
for 8.0 and 8.1 versions we dicided to upgrade from 7.4 to 8.1. I must say
that the COPY FROM processes are much faster now from 27 to 17 minutes. Some
queries where slower, but the performance problems were solved by increasing
work_mem to 8192.
    However, now we have a query that is much slower with 8.1 compared to 7.4.
The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage
at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4
seconds the following tries.
    We're not experts at all but we can't see anything strange with the
differences of EXPLAIN in the queries. Below I paste the query and the
EXPLAIN output.
    Does somebody have a clue of what could be the cause of this big difference
in performance?
    Many thanks in advance.


SELECT
        lpad(c.codigo,6,'0'),
        MIN(c.nombre),

        SUM( CASE WHEN ( res.hora_inicio >= time '00:00' AND res.hora_inicio <
time '16:00' )
                THEN (CASE WHEN res.importe_neto IS NOT NULL
                        THEN res.importe_neto ELSE 0 END)
                ELSE 0 END ) AS p1,
        SUM( CASE WHEN ( res.hora_inicio >= time '00:00' AND res.hora_inicio <
time '16:00' )
                THEN (CASE WHEN res.cantidad_servida IS NOT NULL
                        THEN res.cantidad_servida
                        ELSE 0 END)
                ELSE 0 END ) AS p2,
        SUM( CASE WHEN ( res.hora_inicio >= time '16:00' AND res.hora_inicio <
time '23:59' )
                THEN (CASE WHEN res.importe_neto IS NOT NULL
                        THEN res.importe_neto
                        ELSE 0 END)
                ELSE 0 END ) AS p3
        SUM( CASE WHEN ( res.hora_inicio >= time '16:00' AND res.hora_inicio <
time '23:59' )
                THEN (CASE WHEN res.cantidad_servida IS NOT NULL THEN
                        res.cantidad_servida
                        ELSE 0 END)
                ELSE 0 END ) AS p4
        SUM(CASE WHEN res.importe_neto IS NOT NULL
                THEN res.importe_neto
                ELSE 0 END) AS total,
        SUM(CASE WHEN res.cantidad_servida IS NOT NULL
                THEN res.cantidad_servida
                ELSE 0 END) AS total_lineas
FROM clientes c LEFT JOIN (
        SELECT
                la.cliente as cliente,
                es.hora_inicio as hora_inicio,
                la.albaran as albaran,
                la.cantidad_servida as cantidad_servida,
                la.importe_neto as importe_neto
        FROM  lineas_albaranes la
        LEFT JOIN escaner es ON la.albaran = es.albaran
        WHERE la.fecha_albaran = '20-12-2005' AND la.empresa = 1 AND
la.indicador_factura = 'F'
        ) AS res ON c.codigo = res.cliente, provincias p
WHERE p.codigo = c.provincia AND p.nombre='NAME' AND EXISTS(SELECT 1 FROM
lineas_albaranes la WHERE la.cliente=c.codigo AND la.fecha_albaran > (date
'20-12-2005' - interval '2 month') AND la.fecha_albaran <= '20-12-2005' AND
la.empresa=1 AND la.indicador_factura='F')
GROUP BY c.codigo
ORDER BY nom;

PostgreSQL 8.1.1:


QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=333769.99..333769.99 rows=2 width=61)
   Sort Key: min((c.nombre)::text)
   ->  GroupAggregate  (cost=37317.41..333769.98 rows=2 width=61)
         ->  Nested Loop  (cost=37317.41..333769.83 rows=2 width=61)
               Join Filter: ("inner".codigo = "outer".provincia)
               ->  Merge Left Join  (cost=37315.27..333758.58 rows=405
width=65)
                     Merge Cond: ("outer".codigo = "inner".cliente)
                     ->  Index Scan using clientes_pkey on clientes c
(cost=0.00..296442.28 rows=405 width=40)
                           Filter: (subplan)
                           SubPlan
                             ->  Bitmap Heap Scan on lineas_albaranes la
(cost=138.99..365.53 rows=1 width=0)
                                   Recheck Cond: ((cliente = $0) AND
((indicador_factura)::text = 'F'::text))
                                   Filter: ((fecha_albaran > '2005-10-20
00:00:00'::timestamp without time zone) AND (fecha_albaran <=
'2005-12-20'::date)AND (empresa = 1))
                                   ->  BitmapAnd  (cost=138.99..138.99 rows=57
width=0)
                                         ->  Bitmap Index Scan on
lineas_albaranes_cliente_idx  (cost=0.00..65.87 rows=11392 width=0)
                                               Index Cond: (cliente = $0)
                                         ->  Bitmap Index Scan on
lineas_albaranes_indicador_factura_idx  (cost=0.00..72.87 rows=11392 width=0)
                                               Index Cond:
((indicador_factura)::text = 'F'::text)
                     ->  Sort  (cost=37315.27..37315.28 rows=1 width=29)
                           Sort Key: la.cliente
                           ->  Nested Loop Left Join  (cost=72.87..37315.26
rows=1 width=29)
                                 ->  Bitmap Heap Scan on lineas_albaranes la
(cost=72.87..37309.24 rows=1 width=25)
                                       Recheck Cond:
((indicador_factura)::text = 'F'::text)
                                       Filter: ((fecha_albaran =
'2005-12-20'::date) AND (empresa = 1))
                                       ->  Bitmap Index Scan on
lineas_albaranes_indicador_factura_idx  (cost=0.00..72.87 rows=11392 width=0)
                                             Index Cond:
((indicador_factura)::text = 'F'::text)
                                 ->  Index Scan using escaner_pkey on escaner
es  (cost=0.00..6.01 rows=1 width=12)
                                       Index Cond: ("outer".albaran =
es.albaran)
               ->  Materialize  (cost=2.14..2.15 rows=1 width=4)
                     ->  Seq Scan on provincias p  (cost=0.00..2.14 rows=1
width=4)
                           Filter: ((nombre)::text = 'NAME'::text)
(31 rows)


PostgreSQL 7.4.7:


                            QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=270300.14..270300.21 rows=29 width=61)
   Sort Key: min((c.nombre)::text)
   ->  HashAggregate  (cost=270298.20..270299.44 rows=29 width=61)
         ->  Hash Join  (cost=270222.84..270297.62 rows=29 width=61)
               Hash Cond: ("outer".provincia = "inner".codigo)
               ->  Merge Left Join  (cost=270220.70..270280.70 rows=2899
width=65)
                     Merge Cond: ("outer".codigo = "inner".cliente)
                     ->  Sort  (cost=10928.47..10929.48 rows=405 width=40)
                           Sort Key: c.codigo
                           ->  Seq Scan on clientes c  (cost=0.00..10910.93
rows=405 width=40)
                                 Filter: (subplan)
                                 SubPlan
                                   ->  Index Scan using
lineas_albaranes_cliente_idx on lineas_albaranes la  (cost=0.00..51542.10
rows=3860 width=0)
                                         Index Cond: (cliente = $0)
                                         Filter: (((fecha_albaran)::timestamp
without time zone > '2005-10-20 00:00:00'::timestamp without time zone) AND
(fecha_albaran <= '2005-12-20'::date) AND (empresa = 1) AND
((indicador_factura)::text = 'F'::text))
                     ->  Sort  (cost=259292.23..259306.72 rows=5797 width=29)
                           Sort Key: la.cliente
                           ->  Merge Right Join  (cost=256176.76..258929.88
rows=5797 width=29)
                                 Merge Cond: ("outer".albaran =
"inner".albaran)
                                 ->  Index Scan using escaner_pkey on escaner
es  (cost=0.00..2582.64 rows=55604 width=12)
                                 ->  Sort  (cost=256176.76..256191.26
rows=5797 width=25)
                                       Sort Key: la.albaran
                                       ->  Seq Scan on lineas_albaranes la
(cost=0.00..255814.42 rows=5797 width=25)
                                             Filter: ((fecha_albaran =
'2005-12-20'::date) AND (empresa = 1) AND ((indicador_factura)::text =
'F'::text))
               ->  Hash  (cost=2.14..2.14 rows=2 width=4)
                     ->  Seq Scan on provincias p  (cost=0.00..2.14 rows=2
width=4)
                           Filter: ((nombre)::text = 'NAME'::text)
(27 rows)