Thread: Slow SQL query (14-15 seconds)
Hello ! Sorry for the subject, I didn't found a better one ! :-/ I'm having a problem with this query (below) that takes betweend 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. If the lines are too long, your mailreader may cut them and make the SQL query and the query plan unreadable, so I've put a copy of them on pastebin.com : <http://pastebin.com/m53ca365> Can you give me some tips to see which part of the query is guilty ? Many thanks in advance for any tips to solve that slowness ! #################################### 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.is_deleted = FALSE AND EXISTS ( SELECT 1 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.pk_societe_id = societes.pk_societe_id ) ORDER BY LOWER(denomination_commerciale); #################################### Here's an EXPLAIN ANALYZE of that query : QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=189404.60..189405.63 rows=414 width=147) (actual time=13614.677..13615.138 rows=285 loops=1) Sort Key: lower((societes.denomination_commerciale)::text) -> Hash Left Join (cost=695.29..189386.60 rows=414 width=147) (actual time=143.767..13612.052 rows=285 loops=1) Hash Cond: ("outer".pk_societe_id = "inner".societe_id) -> Merge Left Join (cost=640.55..189226.33 rows=414 width=139) (actual time=132.203..13598.267 rows=285 loops=1) Merge Cond: ("outer".pk_societe_id = "inner".societe_id) -> Merge Left Join (cost=549.82..189126.52 rows=414 width=131) (actual time=120.373..13581.980 rows=285 loops=1) Merge Cond: ("outer".pk_societe_id = "inner".societe_id) -> Index Scan using pkey_societe_id on societes (cost=0.00..188566.96 rows=414 width=123) (actual time=53.993..13511.770 rows=285 loops=1) Filter: ((NOT is_deleted) AND (subplan)) SubPlan -> Nested Loop (cost=35.56..378.16 rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818) -> Nested Loop (cost=35.56..368.82 rows=2 width=8) (actual time=16.504..16.504 rows=0 loops=818) Join Filter: ("inner".fk_client_id = "outer".pk_client_id) -> Seq Scan on clients (cost=0.00..69.69 rows=1 width=16) (actual time=0.255..0.474 rows=1 loops=818) Filter: ($0 = fk_societe_id) -> Bitmap Heap Scan on commandes (cost=35.56..264.64 rows=2759 width=8) (actual time=6.119..10.385 rows=2252 loops=911) Recheck Cond: ((delivery_date_livraison >= (now() - '1 year'::interval)) AND (delivery_date_livraison <= now())) -> Bitmap Index Scan on idx_date_livraison (cost=0.00..35.56 rows=2759 width=0) (actual time=6.097..6.097 rows=3109 loops=911) Index Cond: ((delivery_date_livraison >= (now() - '1 year'::interval)) AND (delivery_date_livraison <= now())) -> Index Scan using pkey_societe_id on societes societe_client (cost=0.00..4.66 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=285) Index Cond: (pk_societe_id = $0) -> Sort (cost=549.82..552.10 rows=911 width=16) (actual time=66.362..67.343 rows=562 loops=1) Sort Key: stats_commandes.societe_id -> Subquery Scan stats_commandes (cost=484.54..505.04 rows=911 width=16) (actual time=61.656..64.737 rows=563 loops=1) -> HashAggregate (cost=484.54..495.93 rows=911 width=16) (actual time=61.651..62.790 rows=563 loops=1) -> Hash Join (cost=135.22..457.01 rows=5506 width=16) (actual time=13.889..49.362 rows=5958 loops=1) Hash Cond: ("outer".fk_client_id = "inner".pk_client_id) -> Seq Scan on commandes (cost=0.00..233.50 rows=6650 width=16) (actual time=0.003..12.145 rows=5958 loops=1) -> Hash (cost=132.46..132.46 rows=1105 width=16) (actual time=13.855..13.855 rows=1082 loops=1) -> Hash Join (cost=48.39..132.46 rows=1105 width=16) (actual time=4.088..11.448 rows=1082 loops=1) Hash Cond: ("outer".fk_societe_id = "inner".pk_societe_id) -> Seq Scan on clients (cost=0.00..66.35 rows=1335 width=16) (actual time=0.004..2.644 rows=1308 loops=1) -> Hash (cost=46.11..46.11 rows=911 width=8) (actual time=4.051..4.051 rows=903 loops=1) -> Seq Scan on societes (cost=0.00..46.11 rows=911 width=8) (actual time=0.009..2.074 rows=903 loops=1) -> Sort (cost=90.72..92.83 rows=844 width=16) (actual time=11.784..13.245 rows=883 loops=1) Sort Key: stats_adresses_livraison.societe_id -> Subquery Scan stats_adresses_livraison (cost=30.71..49.70 rows=844 width=16) (actual time=4.724..9.537 rows=885 loops=1) -> HashAggregate (cost=30.71..41.26 rows=844 width=16) (actual time=4.719..6.486 rows=885 loops=1) -> Seq Scan on societes_adresses_livraison (cost=0.00..25.90 rows=962 width=16) (actual time=0.010..2.328 rows=991 loops=1) Filter: (NOT is_deleted) -> Hash (cost=52.48..52.48 rows=903 width=16) (actual time=11.507..11.507 rows=903 loops=1) -> Subquery Scan stats_adresses_facturation (cost=32.16..52.48 rows=903 width=16) (actual time=4.604..9.510 rows=903 loops=1) -> HashAggregate (cost=32.16..43.45 rows=903 width=16) (actual time=4.600..6.399 rows=903 loops=1) -> Seq Scan on societes_adresses_facturation (cost=0.00..27.25 rows=983 width=16) (actual time=0.009..2.297 rows=943 loops=1) Filter: (NOT is_deleted) Total runtime: 13618.033 ms (47 lignes) #################################### Regards, -- Bruno Baguette
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.
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.
In any case, it appears that the time is being taken performing a full
index scan over the societe table, in one of the subqueries. Perhaps you
could run each of the subqueries individually, and send us the one that
takes loads of time as a simpler problem to solve.
Matthew
--
Those who do not understand Unix are condemned to reinvent it, poorly.
-- Henry Spencer
Bruno Baguette <bruno.baguette@gmail.com> writes:
> I'm having a problem with this query (below) that takes betweend 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.
It's the repeatedly executed EXISTS subplan that's hurting you:
> SubPlan
> -> Nested Loop (cost=35.56..378.16
> rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818)
16.511 * 818 = 13505.998, so this is all but about 100 msec of the
runtime. Can't tell if there's any easy way to improve it. In
pre-8.4 releases trying to convert the EXISTS into an IN might help.
regards, tom lane
Bruno Baguette napisal 13.11.2008 12:02: > Hello ! > > Sorry for the subject, I didn't found a better one ! :-/ > > I'm having a problem with this query (below) that takes betweend 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. > > If the lines are too long, your mailreader may cut them and make the SQL > query and the query plan unreadable, so I've put a copy of them on > pastebin.com : <http://pastebin.com/m53ca365> > > Can you give me some tips to see which part of the query is guilty ? 1. Your explain analyze points to a lot of loops in exists clause: Filter: ((NOT is_deleted) AND (subplan)) 16.5msec * 800loops = ~13sec. Try to replace exists() with in() or inner joins/distinct. 2. Those 3 left joins can be replaced with subselects: select (select count(*)... ) as societe_nbre_commandes from societes ... -- Regards, Tomasz Myrta
Le 13/11/08 14:31, Tom Lane a écrit :
> It's the repeatedly executed EXISTS subplan that's hurting you:
>
>> SubPlan
>> -> Nested Loop (cost=35.56..378.16
>> rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818)
>
> 16.511 * 818 = 13505.998, so this is all but about 100 msec of the
> runtime. Can't tell if there's any easy way to improve it. In
> pre-8.4 releases trying to convert the EXISTS into an IN might help.
Hello Tom !
If I replace the EXISTS by a IN subquery, it falls from 14-15 seconds to
5 seconds !
####################################
AND EXISTS (
SELECT 1 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.pk_societe_id =
societes.pk_societe_id
)
####################################
replaced by a IN subquery
####################################
AND societes.pk_societe_id IN (
SELECT societes.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()
)
####################################
Heres's the EXPLAIN ANALYZE of the new SQL query :
####################################
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=280995.27..280996.30 rows=414 width=147) (actual
time=5164.297..5165.638 rows=818 loops=1)
Sort Key: lower((societes.denomination_commerciale)::text)
-> Hash Left Join (cost=697.38..280977.27 rows=414 width=147)
(actual time=110.093..5156.853 rows=818 loops=1)
Hash Cond: ("outer".pk_societe_id = "inner".societe_id)
-> Merge Left Join (cost=642.64..280817.00 rows=414
width=139) (actual time=98.886..5141.305 rows=818 loops=1)
Merge Cond: ("outer".pk_societe_id = "inner".societe_id)
-> Merge Left Join (cost=551.92..280717.18 rows=414
width=131) (actual time=87.278..5123.133 rows=818 loops=1)
Merge Cond: ("outer".pk_societe_id =
"inner".societe_id)
-> Index Scan using pkey_societe_id on societes
(cost=0.00..280155.54 rows=414 width=123) (actual time=21.748..5051.976
rows=818 loops=1)
Filter: ((NOT is_deleted) AND (subplan))
SubPlan
-> Hash Join (cost=170.88..438.17
rows=2298 width=0) (actual time=6.165..6.165 rows=1 loops=818)
Hash Cond: ("outer".fk_client_id =
"inner".pk_client_id)
-> Bitmap Heap Scan on commandes
(cost=35.66..266.10 rows=2775 width=8) (actual time=6.144..6.144 rows=1
loops=818)
Recheck Cond:
((delivery_date_livraison >= (now() - '1 year'::interval)) AND
(delivery_date_livraison <= now()))
-> Bitmap Index Scan on
idx_date_livraison (cost=0.00..35.66 rows=2775 width=0) (actual
time=6.121..6.121 rows=3109 loops=818)
Index Cond:
((delivery_date_livraison >= (now() - '1 year'::interval)) AND
(delivery_date_livraison <= now()))
-> Hash (cost=132.46..132.46
rows=1105 width=8) (actual time=13.573..13.573 rows=1082 loops=1)
-> Hash Join
(cost=48.39..132.46 rows=1105 width=8) (actual time=3.933..11.246
rows=1082 loops=1)
Hash Cond:
("outer".fk_societe_id = "inner".pk_societe_id)
-> Seq Scan on clients
(cost=0.00..66.35 rows=1335 width=16) (actual time=0.004..2.623
rows=1308 loops=1)
-> Hash
(cost=46.11..46.11 rows=911 width=8) (actual time=3.900..3.900 rows=903
loops=1)
-> Seq Scan on
societes societe_client (cost=0.00..46.11 rows=911 width=8) (actual
time=0.004..1.947 rows=903 loops=1)
-> Sort (cost=551.92..554.20 rows=911 width=16)
(actual time=65.518..66.453 rows=563 loops=1)
Sort Key: stats_commandes.societe_id
-> Subquery Scan stats_commandes
(cost=486.64..507.14 rows=911 width=16) (actual time=61.034..64.117
rows=563 loops=1)
-> HashAggregate
(cost=486.64..498.03 rows=911 width=16) (actual time=61.028..62.177
rows=563 loops=1)
-> Hash Join
(cost=135.22..458.94 rows=5539 width=16) (actual time=13.517..48.643
rows=5971 loops=1)
Hash Cond:
("outer".fk_client_id = "inner".pk_client_id)
-> Seq Scan on commandes
(cost=0.00..234.90 rows=6690 width=16) (actual time=0.004..11.951
rows=5971 loops=1)
-> Hash
(cost=132.46..132.46 rows=1105 width=16) (actual time=13.486..13.486
rows=1082 loops=1)
-> Hash Join
(cost=48.39..132.46 rows=1105 width=16) (actual time=3.827..11.123
rows=1082 loops=1)
Hash Cond:
("outer".fk_societe_id = "inner".pk_societe_id)
-> Seq Scan
on clients (cost=0.00..66.35 rows=1335 width=16) (actual
time=0.003..2.566 rows=1308 loops=1)
-> Hash
(cost=46.11..46.11 rows=911 width=8) (actual time=3.802..3.802 rows=903
loops=1)
-> Seq
Scan on societes (cost=0.00..46.11 rows=911 width=8) (actual
time=0.004..1.906 rows=903 loops=1)
-> Sort (cost=90.72..92.83 rows=844 width=16) (actual
time=11.566..13.070 rows=885 loops=1)
Sort Key: stats_adresses_livraison.societe_id
-> Subquery Scan stats_adresses_livraison
(cost=30.71..49.70 rows=844 width=16) (actual time=4.504..9.357 rows=885
loops=1)
-> HashAggregate (cost=30.71..41.26
rows=844 width=16) (actual time=4.499..6.304 rows=885 loops=1)
-> Seq Scan on
societes_adresses_livraison (cost=0.00..25.90 rows=962 width=16)
(actual time=0.005..2.221 rows=991 loops=1)
Filter: (NOT is_deleted)
-> Hash (cost=52.48..52.48 rows=903 width=16) (actual
time=11.164..11.164 rows=903 loops=1)
-> Subquery Scan stats_adresses_facturation
(cost=32.16..52.48 rows=903 width=16) (actual time=4.339..9.220 rows=903
loops=1)
-> HashAggregate (cost=32.16..43.45 rows=903
width=16) (actual time=4.334..6.116 rows=903 loops=1)
-> Seq Scan on
societes_adresses_facturation (cost=0.00..27.25 rows=983 width=16)
(actual time=0.006..2.128 rows=943 loops=1)
Filter: (NOT is_deleted)
Total runtime: 5167.896 ms
(48 lignes)
####################################
Many thanks for the help, that's already better (3x time faster) !
Can you explain why a IN is fastest than an EXISTS subquery ? Until now,
I was thinking that IN would require PostgreSQL to scan all the table
(from the beginning to the end) and that EXISTS would require to scan
all the table (from the beginning until getting one match).
Do you think I can improve again the performance of that query ? I
expected more speed since theses are little tables
delivery=> SELECT COUNT(*) FROM societes;
count
-------
903
(1 ligne)
delivery=> SELECT COUNT(*) FROM clients;
count
-------
1308
(1 ligne)
delivery=> SELECT COUNT(*) FROM commandes;
count
-------
5972
(1 ligne)
One reader told me Gmail was guilty for cutting the lines, so I've put a
copy of the query plan on pastebin.com to keep it readable :
<http://pastebin.com/m6434f639>
Thanks in advance for any tips !
Regards,
--
Bruno Baguette
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
Bruno Baguette <bruno.baguette@gmail.com> writes:
> Le 13/11/08 14:31, Tom Lane a �crit :
>> 16.511 * 818 = 13505.998, so this is all but about 100 msec of the
>> runtime. Can't tell if there's any easy way to improve it. In
>> pre-8.4 releases trying to convert the EXISTS into an IN might help.
> Can you explain why a IN is fastest than an EXISTS subquery ?
The planner is smarter about IN than EXISTS --- it can usually convert
the former into a join plan instead of a subplan. (This situation will
improve in 8.4.)
> Do you think I can improve again the performance of that query ?
You've still got a subplan in there, not quite sure why. Anyway,
increasing work_mem might get it to change to a hashed subplan,
which'd likely be faster.
regards, tom lane
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
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