explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MdZZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 352.245 ↑ 1.0 1 1

Aggregate (cost=539,341.49..539,341.50 rows=1 width=8) (actual time=352.243..352.245 rows=1 loops=1)

2. 0.076 352.202 ↑ 6.9 21 1

Unique (cost=539,329.25..539,339.69 rows=144 width=531) (actual time=352.098..352.202 rows=21 loops=1)

3. 0.264 352.126 ↑ 6.9 21 1

Sort (cost=539,329.25..539,329.61 rows=144 width=531) (actual time=352.095..352.126 rows=21 loops=1)

  • Sort Key: po.id, ap.id, ap.asset_purchase_nr, ap.asset_id, ap.request_reference, ap.contract_id, ap.contract_number, ap.date_of_status, ap.proposed_contract_start_date, ap.make, ap.model, po.supplier_id, po.supplier_code, po.supplier_name, ap.customer_id, ap.customer_code, ap.customer_name, ap.requested_start_date, ap.actual_co2, po.stock, ap.alert, ap.alert_note, ap.cancelled, ap.cancellation_date, ap.sla_status, ap.yellow_sla_time, ap.red_sla_time, ap.sla_activity_type
  • Sort Method: quicksort Memory: 40kB
4. 0.161 351.862 ↑ 6.9 21 1

Nested Loop (cost=539,079.53..539,324.08 rows=144 width=531) (actual time=339.637..351.862 rows=21 loops=1)

5. 6.597 351.503 ↑ 9.5 33 1

Hash Join (cost=539,079.25..539,211.24 rows=315 width=474) (actual time=339.613..351.503 rows=33 loops=1)

  • Hash Cond: (pc.asset_purchase_id = ap.id)
6. 6.311 6.311 ↓ 1.0 4,142 1

Seq Scan on purchase_configuration pc (cost=0.00..113.34 rows=4,134 width=32) (actual time=0.063..6.311 rows=4,142 loops=1)

7. 0.063 338.595 ↑ 7.7 22 1

Hash (cost=539,077.13..539,077.13 rows=170 width=458) (actual time=338.594..338.595 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
8. 0.066 338.532 ↑ 7.7 22 1

Subquery Scan on ap (cost=539,073.73..539,077.13 rows=170 width=458) (actual time=338.420..338.532 rows=22 loops=1)

9. 0.154 338.466 ↑ 7.7 22 1

HashAggregate (cost=539,073.73..539,075.43 rows=170 width=538) (actual time=338.416..338.466 rows=22 loops=1)

  • Group Key: ap_1.id, ap_1.asset_purchase_nr, ap_1.request_reference, ap_1.status, ap_1.date_of_status, ap_1.contract_number, ap_1.make, ap_1.model, ap_1.supplier_code, ap_1.supplier_name, ap_1.customer_code, ap_1.customer_name, ap_1.requested_start_date, ap_1.actual_co2, ap_1.cancelled, ap_1.customer_id, ap_1.supplier_id, ap_1.alert, ap_1.alert_note, ap_1.contract_id, ap_1.cancellation_date, ap_1.asset_id, ap_1.proposed_contract_start_date, (CASE WHEN (now() < sla.yellow_sla_time) THEN 'GREEN'::text WHEN ((now() > sla.yellow_sla_time) AND (now() < sla.red_sla_time)) THEN 'YELLOW'::text WHEN (now() > sla.red_sla_time) THEN 'RED'::text ELSE NULL::text END), ((sla.yellow_sla_time)::timestamp with time zone), ((sla.red_sla_time)::timestamp with time zone), sla.activity_type
10. 0.067 338.312 ↑ 7.7 22 1

Append (cost=116.09..539,062.25 rows=170 width=538) (actual time=1.795..338.312 rows=22 loops=1)

11. 0.053 1.898 ↓ 6.5 13 1

Result (cost=116.09..116.26 rows=2 width=538) (actual time=1.792..1.898 rows=13 loops=1)

12. 0.046 1.845 ↓ 6.5 13 1

Unique (cost=116.09..116.23 rows=2 width=538) (actual time=1.780..1.845 rows=13 loops=1)

13. 0.142 1.799 ↓ 6.5 13 1

Sort (cost=116.09..116.09 rows=2 width=538) (actual time=1.777..1.799 rows=13 loops=1)

  • Sort Key: ap_1.id, ap_1.asset_purchase_nr, ap_1.request_reference, ap_1.status, ap_1.date_of_status, ap_1.contract_number, ap_1.make, ap_1.model, ap_1.supplier_code, ap_1.supplier_name, ap_1.customer_code, ap_1.customer_name, ap_1.requested_start_date, ap_1.actual_co2, ap_1.cancelled, ap_1.customer_id, ap_1.supplier_id, ap_1.alert, ap_1.alert_note, ap_1.contract_id, ap_1.cancellation_date, ap_1.asset_id, ap_1.proposed_contract_start_date, (CASE WHEN (now() < sla.yellow_sla_time) THEN 'GREEN'::text WHEN ((now() > sla.yellow_sla_time) AND (now() < sla.red_sla_time)) THEN 'YELLOW'::text WHEN (now() > sla.red_sla_time) THEN 'RED'::text ELSE NULL::text END), sla.yellow_sla_time, sla.red_sla_time, sla.activity_type
  • Sort Method: quicksort Memory: 32kB
14. 0.040 1.657 ↓ 6.5 13 1

Append (cost=109.31..116.08 rows=2 width=538) (actual time=1.262..1.657 rows=13 loops=1)

15. 0.055 1.328 ↓ 12.0 12 1

Unique (cost=109.31..109.37 rows=1 width=588) (actual time=1.258..1.328 rows=12 loops=1)

16. 0.165 1.273 ↓ 12.0 12 1

Sort (cost=109.31..109.31 rows=1 width=588) (actual time=1.255..1.273 rows=12 loops=1)

  • Sort Key: ap_1.id, ap_1.asset_purchase_nr, ap_1.request_reference, ap_1.date_of_status, ap_1.contract_number, ap_1.make, ap_1.model, ap_1.supplier_code, ap_1.supplier_name, ap_1.customer_code, ap_1.customer_name, ap_1.requested_start_date, ap_1.actual_co2, ap_1.cancelled, ap_1.customer_id, ap_1.supplier_id, ap_1.alert, ap_1.alert_note, ap_1.contract_id, ap_1.cancellation_date, ap_1.asset_id, ap_1.proposed_contract_start_date, (CASE WHEN (now() < sla.yellow_sla_time) THEN 'GREEN'::text WHEN ((now() > sla.yellow_sla_time) AND (now() < sla.red_sla_time)) THEN 'YELLOW'::text WHEN (now() > sla.red_sla_time) THEN 'RED'::text ELSE NULL::text END), sla.yellow_sla_time, sla.red_sla_time
  • Sort Method: quicksort Memory: 32kB
17. 0.112 1.108 ↓ 12.0 12 1

Nested Loop (cost=3.29..109.30 rows=1 width=588) (actual time=0.366..1.108 rows=12 loops=1)

18. 0.432 0.516 ↓ 6.0 12 1

Bitmap Heap Scan on sla_task sla (cost=3.01..102.66 rows=2 width=58) (actual time=0.232..0.516 rows=12 loops=1)

  • Recheck Cond: ((activity_type = 'ASSET_PURCHASE_READY_FOR_DECISION'::text) AND (resource_type = 'ASSET_PURCHASE'::text) AND (end_time IS NULL))
  • Filter: (CASE WHEN (now() < yellow_sla_time) THEN 'GREEN'::text WHEN ((now() > yellow_sla_time) AND (now() < red_sla_time)) THEN 'YELLOW'::text WHEN (now() > red_sla_time) THEN 'RED'::text ELSE NULL::text END = ANY ('{GREEN,YELLOW,RED,GREY}'::text[]))
  • Heap Blocks: exact=11
19. 0.084 0.084 ↑ 6.3 14 1

Bitmap Index Scan on sla_activity_resource_time (cost=0.00..3.01 rows=88 width=0) (actual time=0.083..0.084 rows=14 loops=1)

  • Index Cond: ((activity_type = 'ASSET_PURCHASE_READY_FOR_DECISION'::text) AND (resource_type = 'ASSET_PURCHASE'::text) AND (end_time IS NULL))
20. 0.480 0.480 ↑ 1.0 1 12

Index Scan using pk_asset_purchase on asset_purchase ap_1 (cost=0.28..3.30 rows=1 width=514) (actual time=0.038..0.040 rows=1 loops=12)

  • Index Cond: (id = sla.resource_id)
  • Filter: (status = 'ACTIVE'::text)
21. 0.005 0.289 ↑ 1.0 1 1

Unique (cost=6.62..6.68 rows=1 width=588) (actual time=0.283..0.289 rows=1 loops=1)

22. 0.084 0.284 ↑ 1.0 1 1

Sort (cost=6.62..6.62 rows=1 width=588) (actual time=0.280..0.284 rows=1 loops=1)

  • Sort Key: ap_1_1.id, ap_1_1.asset_purchase_nr, ap_1_1.request_reference, ap_1_1.date_of_status, ap_1_1.contract_number, ap_1_1.make, ap_1_1.model, ap_1_1.supplier_code, ap_1_1.supplier_name, ap_1_1.customer_code, ap_1_1.customer_name, ap_1_1.requested_start_date, ap_1_1.actual_co2, ap_1_1.cancelled, ap_1_1.customer_id, ap_1_1.supplier_id, ap_1_1.alert, ap_1_1.alert_note, ap_1_1.contract_id, ap_1_1.cancellation_date, ap_1_1.asset_id, ap_1_1.proposed_contract_start_date, sla_1.yellow_sla_time, sla_1.red_sla_time
  • Sort Method: quicksort Memory: 25kB
23. 0.013 0.200 ↑ 1.0 1 1

Result (cost=0.57..6.61 rows=1 width=588) (actual time=0.183..0.200 rows=1 loops=1)

  • One-Time Filter: ('GREY'::text = ANY ('{GREEN,YELLOW,RED,GREY}'::text[]))
24. 0.009 0.187 ↑ 1.0 1 1

Nested Loop (cost=0.57..6.61 rows=1 width=588) (actual time=0.172..0.187 rows=1 loops=1)

25. 0.114 0.114 ↑ 1.0 1 1

Index Scan using sla_activity_type on sla_task sla_1 (cost=0.29..3.29 rows=1 width=58) (actual time=0.106..0.114 rows=1 loops=1)

  • Index Cond: (activity_type = 'WAITING_FOR_SUPPLIERS'::text)
  • Filter: ((end_time IS NULL) AND (resource_type = 'ASSET_PURCHASE'::text))
  • Rows Removed by Filter: 2
26. 0.064 0.064 ↑ 1.0 1 1

Index Scan using pk_asset_purchase on asset_purchase ap_1_1 (cost=0.28..3.30 rows=1 width=514) (actual time=0.061..0.064 rows=1 loops=1)

  • Index Cond: (id = sla_1.resource_id)
  • Filter: (status = 'ACTIVE'::text)
27. 0.035 336.347 ↑ 18.7 9 1

Unique (cost=538,934.66..538,944.32 rows=168 width=594) (actual time=336.295..336.347 rows=9 loops=1)

28. 0.169 336.312 ↑ 18.7 9 1

Sort (cost=538,934.66..538,935.08 rows=168 width=594) (actual time=336.291..336.312 rows=9 loops=1)

  • Sort Key: ap_1_2.id, ap_1_2.asset_purchase_nr, ap_1_2.request_reference, ap_1_2.date_of_status, ap_1_2.contract_number, ap_1_2.make, ap_1_2.model, ap_1_2.supplier_code, ap_1_2.supplier_name, ap_1_2.customer_code, ap_1_2.customer_name, ap_1_2.requested_start_date, ap_1_2.actual_co2, ap_1_2.cancelled, ap_1_2.customer_id, ap_1_2.supplier_id, ap_1_2.alert, ap_1_2.alert_note, ap_1_2.contract_id, ap_1_2.cancellation_date, ap_1_2.asset_id, ap_1_2.proposed_contract_start_date
  • Sort Method: quicksort Memory: 33kB
29. 0.067 336.143 ↑ 18.7 9 1

Result (cost=12.23..538,928.45 rows=168 width=594) (actual time=27.738..336.143 rows=9 loops=1)

  • One-Time Filter: ('GREEN'::text = ANY ('{GREEN,YELLOW,RED,GREY}'::text[]))
30. 42.189 336.076 ↑ 18.7 9 1

Nested Loop Anti Join (cost=12.23..538,928.45 rows=168 width=594) (actual time=27.727..336.076 rows=9 loops=1)

  • Join Filter: (SubPlan 6)
  • Rows Removed by Join Filter: 6631
31. 11.336 98.758 ↑ 30.6 11 1

Index Scan using idx_status_asset_purchase on asset_purchase ap_1_2 (cost=0.28..95,176.37 rows=337 width=514) (actual time=2.098..98.758 rows=11 loops=1)

  • Index Cond: (status = 'ACTIVE'::text)
  • Filter: ((NOT (SubPlan 5)) AND (((SubPlan 1) > 0) OR (((SubPlan 2) > 0) AND ((SubPlan 3) = (SubPlan 4)))))
  • Rows Removed by Filter: 2006
32.          

SubPlan (forIndex Scan)

33. 16.136 16.136 ↑ 1.0 1 2,017

Index Scan using idx_ir_sla_task_resource on sla_task sla_3 (cost=0.29..3.31 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=2,017)

  • Index Cond: ((resource_id = ap_1_2.id) AND (resource_type = 'ASSET_PURCHASE'::text))
  • Filter: (activity_type = 'ASSET_PURCHASE_READY_FOR_DECISION'::text)
  • Rows Removed by Filter: 1
34. 2.240 20.608 ↑ 1.0 1 448

Aggregate (cost=11.28..11.29 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=448)

35. 5.720 18.368 ↓ 0.0 0 448

Nested Loop (cost=2.08..11.28 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=448)

36. 4.480 6.720 ↑ 1.0 2 448

Bitmap Heap Scan on purchase_configuration pc_1 (cost=1.80..4.66 rows=2 width=16) (actual time=0.011..0.015 rows=2 loops=448)

  • Recheck Cond: (asset_purchase_id = ap_1_2.id)
  • Heap Blocks: exact=521
37. 2.240 2.240 ↑ 1.0 2 448

Bitmap Index Scan on pc_ap (cost=0.00..1.80 rows=2 width=0) (actual time=0.005..0.005 rows=2 loops=448)

  • Index Cond: (asset_purchase_id = ap_1_2.id)
38. 5.928 5.928 ↓ 0.0 0 988

Index Scan using idx_po_pc_id on purchase_order po_1 (cost=0.28..3.30 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=988)

  • Index Cond: (purchase_configuration_id = pc_1.id)
  • Filter: ((NOT cancelled) AND (purchase_order_status = 'DECLINED'::text))
  • Rows Removed by Filter: 1
39. 3.496 20.102 ↑ 1.0 1 437

Aggregate (cost=11.28..11.29 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=437)

40. 6.954 16.606 ↑ 1.0 2 437

Nested Loop (cost=2.08..11.28 rows=2 width=0) (actual time=0.019..0.038 rows=2 loops=437)

41. 3.496 4.807 ↑ 1.0 2 437

Bitmap Heap Scan on purchase_configuration pc_1_1 (cost=1.80..4.66 rows=2 width=16) (actual time=0.007..0.011 rows=2 loops=437)

  • Recheck Cond: (asset_purchase_id = ap_1_2.id)
  • Heap Blocks: exact=510
42. 1.311 1.311 ↑ 1.0 2 437

Bitmap Index Scan on pc_ap (cost=0.00..1.80 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=437)

  • Index Cond: (asset_purchase_id = ap_1_2.id)
43. 4.845 4.845 ↑ 1.0 1 969

Index Scan using idx_po_pc_id on purchase_order po_1_1 (cost=0.28..3.30 rows=1 width=16) (actual time=0.003..0.005 rows=1 loops=969)

  • Index Cond: (purchase_configuration_id = pc_1_1.id)
  • Filter: (NOT cancelled)
  • Rows Removed by Filter: 0
44. 2.016 13.440 ↑ 1.0 1 336

Aggregate (cost=11.28..11.29 rows=1 width=8) (actual time=0.038..0.040 rows=1 loops=336)

45. 4.520 11.424 ↑ 1.0 1 336

Nested Loop (cost=2.08..11.28 rows=1 width=0) (actual time=0.025..0.034 rows=1 loops=336)

46. 2.688 3.696 ↑ 1.0 2 336

Bitmap Heap Scan on purchase_configuration pc_1_2 (cost=1.80..4.66 rows=2 width=16) (actual time=0.007..0.011 rows=2 loops=336)

  • Recheck Cond: (asset_purchase_id = ap_1_2.id)
  • Heap Blocks: exact=387
47. 1.008 1.008 ↑ 1.0 2 336

Bitmap Index Scan on pc_ap (cost=0.00..1.80 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=336)

  • Index Cond: (asset_purchase_id = ap_1_2.id)
48. 3.208 3.208 ↓ 0.0 0 802

Index Scan using idx_po_pc_id on purchase_order po_1_2 (cost=0.28..3.30 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=802)

  • Index Cond: (purchase_configuration_id = pc_1_2.id)
  • Filter: ((NOT cancelled) AND (purchase_order_status = 'ACCEPTED'::text))
  • Rows Removed by Filter: 1
49. 3.024 17.136 ↑ 1.0 1 336

Aggregate (cost=11.28..11.29 rows=1 width=8) (actual time=0.049..0.051 rows=1 loops=336)

50. 6.406 14.112 ↑ 1.0 2 336

Nested Loop (cost=2.08..11.28 rows=2 width=0) (actual time=0.017..0.042 rows=2 loops=336)

51. 2.688 3.696 ↑ 1.0 2 336

Bitmap Heap Scan on purchase_configuration pc_1_3 (cost=1.80..4.66 rows=2 width=16) (actual time=0.007..0.011 rows=2 loops=336)

  • Recheck Cond: (asset_purchase_id = ap_1_2.id)
  • Heap Blocks: exact=387
52. 1.008 1.008 ↑ 1.0 2 336

Bitmap Index Scan on pc_ap (cost=0.00..1.80 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=336)

  • Index Cond: (asset_purchase_id = ap_1_2.id)
53. 4.010 4.010 ↑ 1.0 1 802

Index Scan using idx_po_pc_id on purchase_order po_1_3 (cost=0.28..3.30 rows=1 width=16) (actual time=0.003..0.005 rows=1 loops=802)

  • Index Cond: (purchase_configuration_id = pc_1_3.id)
  • Filter: (NOT cancelled)
  • Rows Removed by Filter: 0
54. 11.991 16.038 ↓ 1.4 603 11

Materialize (cost=11.95..285.54 rows=434 width=16) (actual time=0.037..1.458 rows=603 loops=11)

55. 3.709 4.047 ↓ 1.5 656 1

Bitmap Heap Scan on sla_task sla_2 (cost=11.95..283.37 rows=434 width=16) (actual time=0.381..4.047 rows=656 loops=1)

  • Recheck Cond: ((activity_type = 'PURCHASE_REQUEST_SENT'::text) AND (resource_type = 'PURCHASE_REQUEST'::text) AND (end_time IS NULL))
  • Heap Blocks: exact=145
56. 0.338 0.338 ↓ 1.5 665 1

Bitmap Index Scan on sla_activity_resource_time (cost=0.00..11.84 rows=434 width=0) (actual time=0.336..0.338 rows=665 loops=1)

  • Index Cond: ((activity_type = 'PURCHASE_REQUEST_SENT'::text) AND (resource_type = 'PURCHASE_REQUEST'::text) AND (end_time IS NULL))
57.          

SubPlan (forNested Loop Anti Join)

58. 85.718 179.091 ↑ 1.0 2 6,633

Nested Loop (cost=0.56..11.44 rows=2 width=16) (actual time=0.012..0.027 rows=2 loops=6,633)

59. 39.798 39.798 ↑ 1.0 2 6,633

Index Scan using pc_ap on purchase_configuration pc_1_4 (cost=0.28..4.82 rows=2 width=16) (actual time=0.003..0.006 rows=2 loops=6,633)

  • Index Cond: (asset_purchase_id = ap_1_2.id)
60. 53.575 53.575 ↑ 1.0 1 10,715

Index Scan using idx_po_pc_id on purchase_order po_1_4 (cost=0.28..3.30 rows=1 width=32) (actual time=0.003..0.005 rows=1 loops=10,715)

  • Index Cond: (purchase_configuration_id = pc_1_4.id)
61. 0.198 0.198 ↑ 1.0 1 33

Index Scan using idx_po_pc_id on purchase_order po (cost=0.28..0.35 rows=1 width=89) (actual time=0.005..0.006 rows=1 loops=33)

  • Index Cond: (purchase_configuration_id = pc.id)
  • Filter: ((NOT cancelled) AND (purchase_type = 'ASSET'::text))
  • Rows Removed by Filter: 0
Planning time : 23.049 ms