Re: Slow SQL query (14-15 seconds) - Mailing list pgsql-performance

Le 13/11/08 14:28, Matthew Wakeling a écrit :
> On Thu, 13 Nov 2008, Bruno Baguette wrote:
>> I'm having a problem with this query (below) that takes between 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.

Hello Matthew !

> As a general tip, if you're trying to work out which part of a query is
> taking time, and the query is fairly obviously made up of several parts,
> it would make sense to try them individually.

I did a try separately for each LEFT JOIN and EXISTS, but I didn't
understood that the EXISTS was guilty, since it was fast to me (indeed,
I did't saw that it was runned 818 times !).

With Tomasz, Tom and your suggest, I've changed the EXISTS subquery to a
IN subquery (cf. my answer to Tom). The query time was going from 14-15
seconds to ~5 seconds.

I just found an faster way by moving the "AND societe.is_deleted = FALSE
"from the main query to the IN subquery. The query is now running in 165
ms !!!  :-)

Here's the current SQL query :

####################################
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.pk_societe_id IN (
                                   SELECT societe_client.pk_societe_id
                                   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.is_deleted = FALSE
                                 )
ORDER BY LOWER(denomination_commerciale);
####################################

and the query plan :

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

            QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=1311.74..1313.79 rows=821 width=147) (actual
time=162.924..163.400 rows=285 loops=1)
    Sort Key: lower((societes.denomination_commerciale)::text)
    ->  Hash IN Join  (cost=1196.61..1272.00 rows=821 width=147) (actual
time=137.164..160.354 rows=285 loops=1)
          Hash Cond: ("outer".pk_societe_id = "inner".pk_societe_id)
          ->  Merge Left Join  (cost=788.60..837.19 rows=903 width=147)
(actual time=95.140..116.124 rows=903 loops=1)
                Merge Cond: ("outer".pk_societe_id = "inner".societe_id)
                ->  Merge Left Join  (cost=695.31..728.65 rows=903
width=139) (actual time=83.413..97.585 rows=903 loops=1)
                      Merge Cond: ("outer".pk_societe_id =
"inner".societe_id)
                      ->  Merge Left Join  (cost=602.27..620.33 rows=903
width=131) (actual time=71.751..79.176 rows=903 loops=1)
                            Merge Cond: ("outer".pk_societe_id =
"inner".societe_id)
                            ->  Sort  (cost=89.36..91.62 rows=903
width=123) (actual time=5.966..7.494 rows=903 loops=1)
                                  Sort Key: societes.pk_societe_id
                                  ->  Seq Scan on societes
(cost=0.00..45.03 rows=903 width=123) (actual time=0.007..2.775 rows=903
loops=1)
                            ->  Sort  (cost=512.91..515.17 rows=903
width=16) (actual time=65.773..66.726 rows=563 loops=1)
                                  Sort Key: stats_commandes.societe_id
                                  ->  Subquery Scan stats_commandes
(cost=448.26..468.58 rows=903 width=16) (actual time=61.278..64.345
rows=563 loops=1)
                                        ->  HashAggregate
(cost=448.26..459.55 rows=903 width=16) (actual time=61.273..62.413
rows=563 loops=1)
                                              ->  Hash Join
(cost=132.44..423.38 rows=4977 width=16) (actual time=13.740..48.912
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=16) (actual
time=0.004..11.882 rows=5972 loops=1)
                                                    ->  Hash
(cost=129.74..129.74 rows=1083 width=16) (actual time=13.711..13.711
rows=1082 loops=1)
                                                          ->  Hash Join
  (cost=47.29..129.74 rows=1083 width=16) (actual time=3.882..11.315
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.652 rows=1308 loops=1)
                                                                ->  Hash
  (cost=45.03..45.03 rows=903 width=8) (actual time=3.846..3.846
rows=903 loops=1)
                                                                      ->
  Seq Scan on societes  (cost=0.00..45.03 rows=903 width=8) (actual
time=0.004..1.897 rows=903 loops=1)
                      ->  Sort  (cost=93.04..95.21 rows=868 width=16)
(actual time=11.651..13.149 rows=885 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.602..9.398 rows=885
loops=1)
                                  ->  HashAggregate  (cost=31.14..41.99
rows=868 width=16) (actual time=4.598..6.370 rows=885 loops=1)
                                        ->  Seq Scan on
societes_adresses_livraison  (cost=0.00..26.19 rows=990 width=16)
(actual time=0.006..2.225 rows=991 loops=1)
                                              Filter: (NOT is_deleted)
                ->  Sort  (cost=93.29..95.46 rows=866 width=16) (actual
time=11.718..13.221 rows=903 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.502..9.424 rows=903
loops=1)
                            ->  HashAggregate  (cost=31.55..42.38
rows=866 width=16) (actual time=4.498..6.311 rows=903 loops=1)
                                  ->  Seq Scan on
societes_adresses_facturation  (cost=0.00..26.84 rows=943 width=16)
(actual time=0.006..2.180 rows=943 loops=1)
                                        Filter: (NOT is_deleted)
          ->  Hash  (cost=403.31..403.31 rows=1877 width=16) (actual
time=41.623..41.623 rows=2677 loops=1)
                ->  Hash Join  (cost=164.98..403.31 rows=1877 width=16)
(actual time=19.522..35.816 rows=2677 loops=1)
                      Hash Cond: ("outer".fk_client_id =
"inner".pk_client_id)
                      ->  Bitmap Heap Scan on commandes
(cost=33.97..241.06 rows=2493 width=8) (actual time=6.043..11.625
rows=2774 loops=1)
                            Recheck Cond: ((delivery_date_livraison >=
(now() - '1 year'::interval)) AND (delivery_date_livraison <= now()))
                            ->  Bitmap Index Scan on idx_date_livraison
  (cost=0.00..33.97 rows=2493 width=0) (actual time=6.018..6.018
rows=2774 loops=1)
                                  Index Cond: ((delivery_date_livraison
 >= (now() - '1 year'::interval)) AND (delivery_date_livraison <= now()))
                      ->  Hash  (cost=128.55..128.55 rows=985 width=24)
(actual time=13.465..13.465 rows=1016 loops=1)
                            ->  Hash Join  (cost=47.08..128.55 rows=985
width=24) (actual time=4.062..11.293 rows=1016 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.635
rows=1308 loops=1)
                                  ->  Hash  (cost=45.03..45.03 rows=821
width=8) (actual time=4.002..4.002 rows=818 loops=1)
                                        ->  Seq Scan on societes
societe_client  (cost=0.00..45.03 rows=821 width=8) (actual
time=0.006..2.363 rows=818 loops=1)
                                              Filter: (NOT is_deleted)
  Total runtime: 164.639 ms
(53 lignes)

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

To keep the reading easy, I've put a copy on pastebin :
<http://pastebin.com/m33388d93>

Many thanks everybody for your help !

King regards,

--
Bruno Baguette