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.
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.
