Slow SQL query (14-15 seconds)

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