Skip site navigation (1) Skip section navigation (2)

Slow SQL query (14-15 seconds)

From: Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow SQL query (14-15 seconds)
Date: 2008-11-13 11:02:58
Message-ID: 491C0962.9060405@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello !

Sorry for the subject, I didn't found a better one ! :-/

I'm having a problem with this query (below) that takes betweend 14 and 
15 seconds to run, which is too long for the end-user.

I've done a EXPLAIN ANALYZE (below below) but I'm having difficulties to 
see which part of that query is taking so many times.

If the lines are too long, your mailreader may cut them and make the SQL 
query and the query plan unreadable, so I've put a copy of them on 
pastebin.com : <http://pastebin.com/m53ca365>

Can you give me some tips to see which part of the query is guilty ?

Many thanks in advance for any tips to solve that slowness !

####################################
SELECT pk_societe_id,
        denomination_commerciale,
        denomination_sociale,
        numero_client,
        COALESCE(stats_commandes.nombre, 0) AS societe_nbre_commandes,
        COALESCE(stats_adresses_livraison.nombre, 0) AS 
societe_adresses_livraison_quantite,
        COALESCE(stats_adresses_facturation.nombre, 0) AS 
societe_adresses_facturation_quantite,
        COALESCE(NULLIF(admin_email,''), NULLIF(admin_bis_email,''), 
NULLIF(admin_ter_email,''), 'n/a') AS email,
        COALESCE(NULLIF(admin_tel,''), NULLIF(admin_bis_tel,''), 
NULLIF(admin_ter_tel,''), 'n/a') AS telephone,
        remise_permanente,
        is_horeca
FROM societes
LEFT JOIN (
             SELECT societes.pk_societe_id AS societe_id,
                    COUNT(commandes.pk_commande_id) AS nombre
             FROM commandes
             INNER JOIN clients ON commandes.fk_client_id = 
clients.pk_client_id
             INNER JOIN societes ON clients.fk_societe_id = 
societes.pk_societe_id
             GROUP BY societes.pk_societe_id
           ) AS stats_commandes ON stats_commandes.societe_id = 
societes.pk_societe_id
LEFT JOIN (
             SELECT fk_societe_id AS societe_id,
                    COUNT(pk_adresse_livraison_id) AS nombre
             FROM societes_adresses_livraison
             WHERE is_deleted = FALSE
             GROUP BY fk_societe_id
           ) AS stats_adresses_livraison ON 
stats_adresses_livraison.societe_id = societes.pk_societe_id
LEFT JOIN (
             SELECT fk_societe_id AS societe_id,
                    COUNT(pk_adresse_facturation_id) AS nombre
             FROM societes_adresses_facturation
             WHERE is_deleted = FALSE
             GROUP BY fk_societe_id
           ) AS stats_adresses_facturation ON 
stats_adresses_facturation.societe_id = societes.pk_societe_id
WHERE societes.is_deleted = FALSE
AND EXISTS (
              SELECT 1 FROM commandes
              INNER JOIN clients ON commandes.fk_client_id = 
clients.pk_client_id
              INNER JOIN societes AS societe_client ON 
clients.fk_societe_id = societe_client.pk_societe_id
              WHERE delivery_date_livraison BETWEEN (NOW() - '1 
year'::interval) AND NOW() AND societe_client.pk_societe_id = 
societes.pk_societe_id
            )
ORDER BY LOWER(denomination_commerciale);

####################################


Here's an EXPLAIN ANALYZE of that query :

 
            QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=189404.60..189405.63 rows=414 width=147) (actual 
time=13614.677..13615.138 rows=285 loops=1)
    Sort Key: lower((societes.denomination_commerciale)::text)
    ->  Hash Left Join  (cost=695.29..189386.60 rows=414 width=147) 
(actual time=143.767..13612.052 rows=285 loops=1)
          Hash Cond: ("outer".pk_societe_id = "inner".societe_id)
          ->  Merge Left Join  (cost=640.55..189226.33 rows=414 
width=139) (actual time=132.203..13598.267 rows=285 loops=1)
                Merge Cond: ("outer".pk_societe_id = "inner".societe_id)
                ->  Merge Left Join  (cost=549.82..189126.52 rows=414 
width=131) (actual time=120.373..13581.980 rows=285 loops=1)
                      Merge Cond: ("outer".pk_societe_id = 
"inner".societe_id)
                      ->  Index Scan using pkey_societe_id on societes 
(cost=0.00..188566.96 rows=414 width=123) (actual time=53.993..13511.770 
rows=285 loops=1)
                            Filter: ((NOT is_deleted) AND (subplan))
                            SubPlan
                              ->  Nested Loop  (cost=35.56..378.16 
rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818)
                                    ->  Nested Loop  (cost=35.56..368.82 
rows=2 width=8) (actual time=16.504..16.504 rows=0 loops=818)
                                          Join Filter: 
("inner".fk_client_id = "outer".pk_client_id)
                                          ->  Seq Scan on clients 
(cost=0.00..69.69 rows=1 width=16) (actual time=0.255..0.474 rows=1 
loops=818)
                                                Filter: ($0 = fk_societe_id)
                                          ->  Bitmap Heap Scan on 
commandes  (cost=35.56..264.64 rows=2759 width=8) (actual 
time=6.119..10.385 rows=2252 loops=911)
                                                Recheck Cond: 
((delivery_date_livraison >= (now() - '1 year'::interval)) AND 
(delivery_date_livraison <= now()))
                                                ->  Bitmap Index Scan on 
idx_date_livraison  (cost=0.00..35.56 rows=2759 width=0) (actual 
time=6.097..6.097 rows=3109 loops=911)
                                                      Index Cond: 
((delivery_date_livraison >= (now() - '1 year'::interval)) AND 
(delivery_date_livraison <= now()))
                                    ->  Index Scan using pkey_societe_id 
on societes societe_client  (cost=0.00..4.66 rows=1 width=8) (actual 
time=0.006..0.006 rows=1 loops=285)
                                          Index Cond: (pk_societe_id = $0)
                      ->  Sort  (cost=549.82..552.10 rows=911 width=16) 
(actual time=66.362..67.343 rows=562 loops=1)
                            Sort Key: stats_commandes.societe_id
                            ->  Subquery Scan stats_commandes 
(cost=484.54..505.04 rows=911 width=16) (actual time=61.656..64.737 
rows=563 loops=1)
                                  ->  HashAggregate 
(cost=484.54..495.93 rows=911 width=16) (actual time=61.651..62.790 
rows=563 loops=1)
                                        ->  Hash Join 
(cost=135.22..457.01 rows=5506 width=16) (actual time=13.889..49.362 
rows=5958 loops=1)
                                              Hash Cond: 
("outer".fk_client_id = "inner".pk_client_id)
                                              ->  Seq Scan on commandes 
  (cost=0.00..233.50 rows=6650 width=16) (actual time=0.003..12.145 
rows=5958 loops=1)
                                              ->  Hash 
(cost=132.46..132.46 rows=1105 width=16) (actual time=13.855..13.855 
rows=1082 loops=1)
                                                    ->  Hash Join 
(cost=48.39..132.46 rows=1105 width=16) (actual time=4.088..11.448 
rows=1082 loops=1)
                                                          Hash Cond: 
("outer".fk_societe_id = "inner".pk_societe_id)
                                                          ->  Seq Scan 
on clients  (cost=0.00..66.35 rows=1335 width=16) (actual 
time=0.004..2.644 rows=1308 loops=1)
                                                          ->  Hash 
(cost=46.11..46.11 rows=911 width=8) (actual time=4.051..4.051 rows=903 
loops=1)
                                                                ->  Seq 
Scan on societes  (cost=0.00..46.11 rows=911 width=8) (actual 
time=0.009..2.074 rows=903 loops=1)
                ->  Sort  (cost=90.72..92.83 rows=844 width=16) (actual 
time=11.784..13.245 rows=883 loops=1)
                      Sort Key: stats_adresses_livraison.societe_id
                      ->  Subquery Scan stats_adresses_livraison 
(cost=30.71..49.70 rows=844 width=16) (actual time=4.724..9.537 rows=885 
loops=1)
                            ->  HashAggregate  (cost=30.71..41.26 
rows=844 width=16) (actual time=4.719..6.486 rows=885 loops=1)
                                  ->  Seq Scan on 
societes_adresses_livraison  (cost=0.00..25.90 rows=962 width=16) 
(actual time=0.010..2.328 rows=991 loops=1)
                                        Filter: (NOT is_deleted)
          ->  Hash  (cost=52.48..52.48 rows=903 width=16) (actual 
time=11.507..11.507 rows=903 loops=1)
                ->  Subquery Scan stats_adresses_facturation 
(cost=32.16..52.48 rows=903 width=16) (actual time=4.604..9.510 rows=903 
loops=1)
                      ->  HashAggregate  (cost=32.16..43.45 rows=903 
width=16) (actual time=4.600..6.399 rows=903 loops=1)
                            ->  Seq Scan on 
societes_adresses_facturation  (cost=0.00..27.25 rows=983 width=16) 
(actual time=0.009..2.297 rows=943 loops=1)
                                  Filter: (NOT is_deleted)
  Total runtime: 13618.033 ms
(47 lignes)


####################################

Regards,

-- 
Bruno Baguette

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2008-11-13 13:05:55
Subject: Re: Performance Question
Previous:From: Dave PageDate: 2008-11-13 08:55:04
Subject: Re: Performance Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group