Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9A3D364FD64 for ; Thu, 13 Nov 2008 10:29:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18533-09 for ; Thu, 13 Nov 2008 10:29:12 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.30]) by mail.postgresql.org (Postfix) with ESMTP id C6DA264FC39 for ; Thu, 13 Nov 2008 10:29:12 -0400 (AST) Received: by yx-out-2324.google.com with SMTP id 8so396382yxb.73 for ; Thu, 13 Nov 2008 06:29:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=HmevZVx403IGHoNZi9o/Y+rwPl3LbL04FnJvZRw9zfs=; b=gV1fiyIC/8NIwXncmglpozu7Ly7pkhnXTpBh8PZYxkZ5Lc8ljT27GPXSl3SaI8LhSR +GZxgcKofsaneUkR+mw67Of5jCmEtr/6XrFHaHM3TTd7nbeiktSfMzh+muvcWgRt6dcR vOHNSNHYNQbOUdSyhDoxw+k+Wkv2wWlUnNnxI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=Qo8ViXr/WyXLA57MqeydlBkrSWn8050LMkyOPEtJ8q14TasnbxnlXKU0d94+c0IlFU FWlAOtHqQy7bCNf4vF1CG2BOfYmaAGzMMytTu67IKbTo3rtufoVBJBY4iHCNZYiOTvzq EXPQpI1xPMoRE771DcaDKhjALAq1caV9USozA= Received: by 10.143.2.19 with SMTP id e19mr1260720wfi.152.1226586550613; Thu, 13 Nov 2008 06:29:10 -0800 (PST) Received: by 10.142.79.21 with HTTP; Thu, 13 Nov 2008 06:29:10 -0800 (PST) Message-ID: <1d709ecc0811130629j3a831fdcod67c3c4e8e33c9cb@mail.gmail.com> Date: Thu, 13 Nov 2008 06:29:10 -0800 From: "Vladimir Sitnikov" To: "Bruno Baguette" Subject: Re: Slow SQL query (14-15 seconds) Cc: pgsql-performance@postgresql.org In-Reply-To: <491C0962.9060405@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37850_4741191.1226586550574" References: <491C0962.9060405@gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/126 X-Sequence-Number: 31857 ------=_Part_37850_4741191.1226586550574 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_37850_4741191.1226586550574 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_37850_4741191.1226586550574--