Dear all,
We are facing a performance issue with the following query. Executing this
query takes about 20 seconds.
(the database version is 14.1)
The query:
----- SLOW QUERY -----
SELECT lead_record.id AS id
FROM "lead_record" lead_record
LEFT JOIN "lead_record__field_msisdn"
"lead_record__field_msisdn" ON "lead_record".id =
"lead_record__field_msisdn".entity_id
LEFT JOIN "lead_record__field_campaign"
"lead_record__field_campaign" ON "lead_record".id =
"lead_record__field_campaign".entity_id
LEFT JOIN "lead_record__field_number_of_calls"
"lead_record__field_number_of_calls" ON "lead_record".id =
"lead_record__field_number_of_calls".entity_id
LEFT JOIN "lead_record__field_last_call"
"lead_record__field_last_call" ON "lead_record".id =
"lead_record__field_last_call".entity_id
LEFT JOIN "lead_record__field_last_offered_plan"
"lead_record__field_last_offered_plan" ON "lead_record".id =
"lead_record__field_last_offered_plan".entity_id
LEFT JOIN "lead_record__field_status"
"lead_record__field_status" ON "lead_record".id =
"lead_record__field_status".entity_id
LEFT JOIN "lead_record__field_comment_text"
"lead_record__field_comment_text" ON "lead_record".id =
"lead_record__field_comment_text".entity_id
LEFT JOIN "lead_record__field_date_of_visit"
"lead_record__field_date_of_visit" ON "lead_record".id =
"lead_record__field_date_of_visit".entity_id
LEFT JOIN "lead_record__field_current_mf"
"lead_record__field_current_mf" ON "lead_record".id =
"lead_record__field_current_mf".entity_id
LEFT JOIN "lead_record__field_pos_code"
"lead_record__field_pos_code" ON "lead_record".id =
"lead_record__field_pos_code".entity_id
LEFT JOIN "lead_record__field_assignee"
"lead_record__field_assignee" ON "lead_record".id =
"lead_record__field_assignee".entity_id
LEFT JOIN "lead_record__field_checks_passed"
"lead_record__field_checks_passed" ON "lead_record".id =
"lead_record__field_checks_passed".entity_id
LEFT JOIN "lead_record__field_last_offer_name"
"lead_record__field_last_offer_name" ON "lead_record".id =
"lead_record__field_last_offer_name".entity_id
LEFT JOIN "lead_record__field_next_scheduled_call"
"lead_record__field_next_scheduled_call" ON "lead_record".id =
"lead_record__field_next_scheduled_call".entity_id
LEFT JOIN "taxonomy_term_field_data"
"taxonomy_term_field_data_lead_record__field_campaign" ON
"lead_record__field_campaign".field_campaign_target_id =
"taxonomy_term_field_data_lead_record__field_campaign".tid
LEFT JOIN "taxonomy_term__field_active"
"taxonomy_term__field_active" ON
taxonomy_term_field_data_lead_record__field_campaign.tid =
taxonomy_term__field_active.entity_id
LEFT JOIN "users_field_data"
"users_field_data_lead_record__field_assignee" ON
"lead_record__field_assignee".field_assignee_target_id =
"users_field_data_lead_record__field_assignee".uid
LEFT JOIN "taxonomy_term__field_campaign_end_date"
"taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date"
ON "taxonomy_term_field_data_lead_record__field_campaign".tid =
"taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date".entity_id
WHERE
((TO_DATE("taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date".field_campaign_end_date_value,
'YYYY-MM-DDTHH24:MI:SS') >= (now() - INTERVAL '1 days'))
and "taxonomy_term__field_active".field_active_value = 1
and "lead_record__field_assignee".field_assignee_target_id = 140
and "lead_record__field_pos_code".field_pos_code_value =
'100000064'
and
"lead_record__field_checks_passed".field_checks_passed_value = 1
and
"lead_record__field_number_of_calls".field_number_of_calls_value < 10);
This is the execution plan:
----- EXPLAIN ANALYZE -----
Nested Loop Left Join (cost=65337.38..77121.07 rows=1 width=4) (actual
time=27164.156..27209.338 rows=0 loops=1)
-> Nested Loop (cost=65337.11..77120.46 rows=1 width=12) (actual
time=27164.155..27209.337 rows=0 loops=1)
-> Nested Loop (cost=65336.82..77120.15 rows=1 width=36) (actual
time=27164.155..27209.336 rows=0 loops=1)
Join Filter: (taxonomy_term__field_active.entity_id =
lead_record__field_campaign.field_campaign_target_id)
-> Merge Join (cost=3.46..5.64 rows=1 width=16) (actual
time=0.109..0.547 rows=14 loops=1)
Merge Cond: (taxonomy_term__field_active.entity_id =
taxonomy_term_field_data_lead_record__field_campaign__taxonomy_.entity_id)
-> Index Scan using
taxonomy_term__field_active____pkey on taxonomy_term__field_active
(cost=0.28..53.37 rows=25 width=8) (actual time=0.006..0.382 rows=23
loops=1)
Filter: (field_active_value = 1)
Rows Removed by Filter: 1337
-> Sort (cost=3.10..3.16 rows=25 width=8) (actual
time=0.096..0.111 rows=14 loops=1)
Sort Key:
taxonomy_term_field_data_lead_record__field_campaign__taxonomy_.entity_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on
taxonomy_term__field_campaign_end_date
taxonomy_term_field_data_lead_record__field_campaign__taxonomy_
(cost=0.00..2.52 rows=25 width=8) (actual t
ime=0.074..0.088 rows=14 loops=1)
Filter:
(to_date((field_campaign_end_date_value)::text,
'YYYY-MM-DDTHH24:MI:SS'::text) >= (now() - '1 day'::interval))
Rows Removed by Filter: 65
-> Nested Loop Left Join (cost=65333.36..77114.49 rows=1
width=20) (actual time=1940.255..1943.482 rows=0 loops=14)
-> Nested Loop Left Join (cost=65332.94..77114.04
rows=1 width=20) (actual time=1940.255..1943.482 rows=0 loops=14)
-> Nested Loop Left Join
(cost=65332.53..77113.59 rows=1 width=20) (actual time=1940.254..1943.481
rows=0 loops=14)
-> Nested Loop Left Join
(cost=65332.10..77113.13 rows=1 width=20) (actual time=1940.254..1943.481
rows=0 loops=14)
-> Gather (cost=65331.68..77112.68
rows=1 width=20) (actual time=1940.254..1943.481 rows=0 loops=14)
Workers Planned: 2
Workers Launched: 1
-> Nested Loop
(cost=64331.68..76112.58 rows=1 width=20) (actual time=1076.181..1076.182
rows=0 loops=25)
-> Nested Loop
(cost=64331.26..76112.10 rows=1 width=52) (actual time=1076.180..1076.181
rows=0 loops=25)
-> Hash Join
(cost=64330.84..76065.41 rows=99 width=44) (actual time=1076.179..1076.181
rows=0 loops=25)
Hash Cond: (
lead_record.id = lead_record__field_assignee.entity_id)
-> Parallel
Hash Left Join (cost=64071.94..75186.33 rows=165117 width=28) (actual
time=1564.113..2045.479 rows=396506 loops=1
3)
Hash
Cond: (lead_record.id = lead_record__field_comment_text.entity_id)
->
Parallel Hash Left Join (cost=60385.75..70584.84 rows=165117 width=28)
(actual time=1522.981..1849.529 rows=396506 l
oops=13)
Hash Cond: (lead_record.id = lead_record__field_last_offered_plan.entity_id)
-> Parallel Hash Join (cost=56819.26..66289.32 rows=165117 width=28)
(actual time=1484.191..1677.707 rows=396506
loops=13)
Hash Cond: (lead_record__field_campaign.entity_id = lead_record.id)
-> Parallel Seq Scan on lead_record__field_campaign
(cost=0.00..5741.41 rows=165241 width=16) (actual time=
0.007..60.109 rows=396579 loops=13)
-> Parallel Hash (cost=53947.02..53947.02 rows=165219 width=12)
(actual time=1360.127..1360.128 rows=396506
loops=13)
Buckets: 131072 Batches: 8 Memory Usage: 3392kB
-> Parallel Hash Join (cost=33369.97..53947.02 rows=165219
width=12) (actual time=1120.261..1297.656
rows=396506 loops=13)
Hash Cond: (lead_record.id =
lead_record__field_number_of_calls.entity_id)
-> Parallel Hash Left Join (cost=23851.21..41181.82
rows=165343 width=4) (actual time=798.152..
937.445 rows=396579 loops=13)
Hash Cond: (lead_record.id =
lead_record__field_status.entity_id)
-> Parallel Hash Left Join (cost=16358.35..30690.03
rows=165343 width=4) (actual time=520
.228..659.801 rows=396579 loops=13)
Time: 27237.160 ms (00:27.237)
Any assistance to improve the performance would be appreciated.
Thank you in advance.
