From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
To: Bruno Baguette <bruno.baguette@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow SQL query (14-15 seconds)
Date: Thu, 13 Nov 2008 06:29:10 -0800
Message-ID: <1d709ecc0811130629j3a831fdcod67c3c4e8e33c9cb@mail.gmail.com> (raw)
In-Reply-To: <491C0962.9060405@gmail.com>
References: <491C0962.9060405@gmail.com>
Could you please try this one:
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);
Bien a vous,
Vladimir Sitnikov
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sitnikov.vladimir@gmail.com, bruno.baguette@gmail.com
Subject: Re: Slow SQL query (14-15 seconds)
In-Reply-To: <1d709ecc0811130629j3a831fdcod67c3c4e8e33c9cb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox