Re: Slow SQL query (14-15 seconds)

Le 13/11/08 15:29, Vladimir Sitnikov a écrit :
> Could you please try this one:

Hello Vladimir !

Thanks for your suggest ! I've changed a small typo in your SQL query
suggestion (extra comma in the second LEFT JOIN).
Your suggest is fast also (137 ms), but it returns less rows than mine
(39 rows instead of 48). I'm looking to find why there is a difference
between theses queries.

####################################
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,
                   max(case when delivery_date_livraison BETWEEN (NOW()
- '1 year'::interval) AND NOW() then 1 end) AS il_y_avait_un_commande
            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 il_y_avait_un_commande=1
ORDER BY LOWER(denomination_commerciale);
####################################


and the query plan :

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


                       QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=937.72..939.77 rows=821 width=147) (actual
time=136.103..136.586 rows=285 loops=1)
    Sort Key: lower((societes.denomination_commerciale)::text)
    ->  Merge Left Join  (cost=838.25..897.98 rows=821 width=147)
(actual time=119.986..133.567 rows=285 loops=1)
          Merge Cond: ("outer".pk_societe_id = "inner".societe_id)
          ->  Merge Left Join  (cost=744.95..776.07 rows=821 width=139)
(actual time=108.233..117.249 rows=285 loops=1)
                Merge Cond: ("outer".pk_societe_id = "inner".societe_id)
                ->  Merge Join  (cost=651.92..668.75 rows=821 width=131)
(actual time=96.664..101.378 rows=285 loops=1)
                      Merge Cond: ("outer".pk_societe_id =
"inner".societe_id)
                      ->  Sort  (cost=84.77..86.82 rows=821 width=123)
(actual time=5.215..6.612 rows=816 loops=1)
                            Sort Key: societes.pk_societe_id
                            ->  Seq Scan on societes  (cost=0.00..45.03
rows=821 width=123) (actual time=0.009..2.569 rows=818 loops=1)
                                  Filter: (NOT is_deleted)
                      ->  Sort  (cost=567.15..569.40 rows=903 width=16)
(actual time=91.432..91.926 rows=290 loops=1)
                            Sort Key: stats_commandes.societe_id
                            ->  Subquery Scan stats_commandes
(cost=473.15..522.81 rows=903 width=16) (actual time=89.009..90.736
rows=290 loops=1)
                                  ->  HashAggregate
(cost=473.15..513.78 rows=903 width=20) (actual time=89.005..89.714
rows=290 loops=1)
                                        Filter: (max(CASE WHEN
((delivery_date_livraison >= (now() - '1 year'::interval)) AND
(delivery_date_livraison <= now())) THEN 1 ELSE NULL::integer END) = 1)
                                        ->  Hash Join
(cost=132.44..423.38 rows=4977 width=20) (actual time=13.531..51.192
rows=5972 loops=1)
                                              Hash Cond:
("outer".fk_client_id = "inner".pk_client_id)
                                              ->  Seq Scan on commandes
  (cost=0.00..211.11 rows=6011 width=20) (actual time=0.004..12.644
rows=5972 loops=1)
                                              ->  Hash
(cost=129.74..129.74 rows=1083 width=16) (actual time=13.511..13.511
rows=1082 loops=1)
                                                    ->  Hash Join
(cost=47.29..129.74 rows=1083 width=16) (actual time=3.661..11.094
rows=1082 loops=1)
                                                          Hash Cond:
("outer".fk_societe_id = "inner".pk_societe_id)
                                                          ->  Seq Scan
on clients  (cost=0.00..65.08 rows=1308 width=16) (actual
time=0.003..2.655 rows=1308 loops=1)
                                                          ->  Hash
(cost=45.03..45.03 rows=903 width=8) (actual time=3.645..3.645 rows=903
loops=1)
                                                                ->  Seq
Scan on societes  (cost=0.00..45.03 rows=903 width=8) (actual
time=0.003..1.847 rows=903 loops=1)
                ->  Sort  (cost=93.04..95.21 rows=868 width=16) (actual
time=11.525..13.049 rows=883 loops=1)
                      Sort Key: stats_adresses_livraison.societe_id
                      ->  Subquery Scan stats_adresses_livraison
(cost=31.14..50.67 rows=868 width=16) (actual time=4.627..9.393 rows=885
loops=1)
                            ->  HashAggregate  (cost=31.14..41.99
rows=868 width=16) (actual time=4.622..6.366 rows=885 loops=1)
                                  ->  Seq Scan on
societes_adresses_livraison  (cost=0.00..26.19 rows=990 width=16)
(actual time=0.005..2.259 rows=991 loops=1)
                                        Filter: (NOT is_deleted)
          ->  Sort  (cost=93.29..95.46 rows=866 width=16) (actual
time=11.667..13.180 rows=901 loops=1)
                Sort Key: stats_adresses_facturation.societe_id
                ->  Subquery Scan stats_adresses_facturation
(cost=31.55..51.04 rows=866 width=16) (actual time=4.482..9.404 rows=903
loops=1)
                      ->  HashAggregate  (cost=31.55..42.38 rows=866
width=16) (actual time=4.478..6.306 rows=903 loops=1)
                            ->  Seq Scan on
societes_adresses_facturation  (cost=0.00..26.84 rows=943 width=16)
(actual time=0.006..2.174 rows=943 loops=1)
                                  Filter: (NOT is_deleted)
  Total runtime: 137.650 ms
####################################

As usual, I've put a copy on pastebin : <http://pastebin.com/m7611d419>

Regards,

--
Bruno Baguette