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)

Search Discussions

  • Michael Fuhr at Dec 27, 2005 at 5:13 pm

    On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote:
    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.
    Could you post the EXPLAIN ANALYZE output of the query on both
    systems? That'll show how accurate the planner's estimates are.

    Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both
    versions? The row count estimates in the 8.1.1 query differ from
    those in the 7.4.7 query. Are the two versions using the same data
    set?

    Are your configuration settings the same in both versions? You
    mentioned increasing work_mem, but what about others like
    effective_cache_size, random_page_cost, and shared_buffers?

    --
    Michael Fuhr
  • Albert Cervera Areny at Dec 27, 2005 at 6:02 pm

    A Dimarts 27 Desembre 2005 18:13, Michael Fuhr va escriure:
    On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote:
    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.
    Could you post the EXPLAIN ANALYZE output of the query on both
    systems? That'll show how accurate the planner's estimates are.

    Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both
    versions? The row count estimates in the 8.1.1 query differ from
    those in the 7.4.7 query. Are the two versions using the same data
    set?

    Are your configuration settings the same in both versions? You
    mentioned increasing work_mem, but what about others like
    effective_cache_size, random_page_cost, and shared_buffers?
    Hey, thank you for your fast response, I found what the problem was.

    I thought the settings were the same but work_mem was still higher in 7.4,
    30Mb, so I increased 8.1 to 30Mb and it worked faster, down to 17 seconds the
    first time, 2.5 seconds for the others.

    Are there any "rules of thumb" to let a begginer give reasonable values to
    these parameters? Not only work_mem, but also random_page_cost, and so on.
    Are there any tests one can run to determine "good" values?

    Thanks a lot!

    --
    Albert Cervera Areny
    Dept. Informàtica Sedifa, S.L.

    Av. Can Bordoll, 149
    08202 - Sabadell (Barcelona)
    Tel. 93 715 51 11
    Fax. 93 715 51 12

    ====================================================================
    ........................ AVISO LEGAL ............................
    La presente comunicación y sus anexos tiene como destinatario la
    persona a la que va dirigida, por lo que si usted lo recibe
    por error debe notificarlo al remitente y eliminarlo de su
    sistema, no pudiendo utilizarlo, total o parcialmente, para
    ningún fin. Su contenido puede tener información confidencial o
    protegida legalmente y únicamente expresa la opinión del
    remitente. El uso del correo electrónico vía Internet no
    permite asegurar ni la confidencialidad de los mensajes
    ni su correcta recepción. En el caso de que el
    destinatario no consintiera la utilización del correo electrónico,
    deberá ponerlo en nuestro conocimiento inmediatamente.
    ====================================================================
    ........................... DISCLAIMER .............................
    This message and its attachments are intended exclusively for the
    named addressee. If you receive this message in error, please
    immediately delete it from your system and notify the sender. You
    may not use this message or any part of it for any purpose.
    The message may contain information that is confidential or
    protected by law, and any opinions expressed are those of the
    individual sender. Internet e-mail guarantees neither the
    confidentiality nor the proper receipt of the message sent.
    If the addressee of this message does not consent to the use
    of internet e-mail, please inform us inmmediately.
    ====================================================================
  • Frank Wiles at Dec 27, 2005 at 6:56 pm

    On Tue, 27 Dec 2005 19:02:17 +0100 Albert Cervera Areny wrote:

    Are there any "rules of thumb" to let a begginer give reasonable
    values to these parameters? Not only work_mem, but also
    random_page_cost, and so on. Are there any tests one can run to
    determine "good" values?
    Hi Albert,

    There are several online sites that have information related to
    tuning parameters. Here is a list of a few of them:

    http://revsys.com/writings/postgresql-performance.html

    http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

    http://www.powerpostgresql.com/Docs

    http://www.powerpostgresql.com/PerfList

    Hope these help!

    ---------------------------------
    Frank Wiles <frank@wiles.org>
    http://www.wiles.org
    ---------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedDec 27, '05 at 4:16p
activeDec 27, '05 at 6:56p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase