Re: Slow SQL query (14-15 seconds)
От
Vladimir Sitnikov
Тема
Re: Slow SQL query (14-15 seconds)
Дата
Msg-id
1d709ecc0811130629j3a831fdcod67c3c4e8e33c9cb@mail.gmail.com
Ответ на
Slow SQL query (14-15 seconds) (Bruno Baguette)
Список
Дерево обсуждения
Slow SQL query (14-15 seconds) Bruno Baguette <bruno.baguette@gmail.com>
Re: Slow SQL query (14-15 seconds) "Vladimir Sitnikov" <sitnikov.vladimir@gmail.com>
Re: Slow SQL query (14-15 seconds) Bruno Baguette <bruno.baguette@gmail.com>
Re: Slow SQL query (14-15 seconds) Tom Lane <tgl@sss.pgh.pa.us>
Re: Slow SQL query (14-15 seconds) Bruno Baguette <bruno.baguette@gmail.com>
Re: Slow SQL query (14-15 seconds) Tom Lane <tgl@sss.pgh.pa.us>
Re: Slow SQL query (14-15 seconds) Matthew Wakeling <matthew@flymine.org>
Re: Slow SQL query (14-15 seconds) Bruno Baguette <bruno.baguette@gmail.com>
Re: Slow SQL query (14-15 seconds) Tomasz Myrta <jasiek@klaster.net>
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
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
В списке pgsql-performance по дате отправления
