explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fZo2

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 322.363 ↑ 1.0 10 1

Limit (cost=49,289.53..49,556.61 rows=10 width=176) (actual time=316.457..322.363 rows=10 loops=1)

2. 0.551 322.360 ↑ 6,798.1 10 1

Hash Left Join (cost=49,289.53..1,864,889.66 rows=67,981 width=176) (actual time=316.456..322.360 rows=10 loops=1)

  • Hash Cond: (cases.creditor_id = creditors.creditor_id)
  • Filter: ((lower(updates.update) ~~ '%em%'::text) OR (lower(creditors.name) ~~ '%em%'::text) OR ((((cases.id)::text ~~ 'em%'::text) OR ((debtors_on_cases.fi_payment_id_raw)::text ~~ 'em%'::text) OR (lower(cases.creditor_reference_id) ~~ '%em%'::text)) AND (creditors.owner_id = ANY ('{e6559de2-1d37-4b6e-9256-7864fb5b542f,39ba5dbc-15b5-4cbd-80cd-90d7405ce066,7e670e79-a27f-4975-928a-d25096bd5dfb,70c60b48-24cd-483e-96b5-4739a74fcb41,da4d4600-f7c5-4cf9-86c6-429f05750a50,001502df-3d4e-4918-b384-e4f71859caa4}'::uuid[]))))
  • Rows Removed by Filter: 317
3. 0.116 321.611 ↑ 423.7 327 1

Merge Left Join (cost=49,286.41..132,203.18 rows=138,538 width=160) (actual time=315.922..321.611 rows=327 loops=1)

  • Merge Cond: (cases.case_id = debtors_on_cases.case_id)
4. 0.060 321.050 ↑ 422.7 326 1

Merge Left Join (cost=49,285.99..119,604.58 rows=137,799 width=156) (actual time=315.911..321.050 rows=326 loops=1)

  • Merge Cond: (cases.case_id = updates.case_id)
5. 0.130 318.083 ↑ 422.7 326 1

Merge Left Join (cost=49,285.56..89,268.54 rows=137,799 width=137) (actual time=315.781..318.083 rows=326 loops=1)

  • Merge Cond: (cases.case_id = debts.case_id)
6. 0.458 0.458 ↑ 422.7 326 1

Index Scan using cases_pkey on cases (cost=0.42..14,298.80 rows=137,799 width=73) (actual time=0.004..0.458 rows=326 loops=1)

7. 0.658 317.495 ↑ 234.8 325 1

Finalize GroupAggregate (cost=49,285.14..72,907.98 rows=76,323 width=80) (actual time=315.774..317.495 rows=325 loops=1)

  • Group Key: debts.case_id
8. 0.000 316.837 ↑ 271.6 562 1

Gather Merge (cost=49,285.14..69,855.06 rows=152,646 width=80) (actual time=315.742..316.837 rows=562 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 1.620 899.589 ↑ 143.5 532 3

Partial GroupAggregate (cost=48,285.12..51,235.90 rows=76,323 width=80) (actual time=299.235..299.863 rows=532 loops=3)

  • Group Key: debts.case_id
10. 220.086 897.969 ↑ 216.5 834 3

Sort (cost=48,285.12..48,736.60 rows=180,593 width=53) (actual time=299.225..299.323 rows=834 loops=3)

  • Sort Key: debts.case_id
  • Sort Method: external merge Disk: 8720kB
11. 198.399 677.883 ↑ 1.2 144,628 3

Hash Left Join (cost=10,130.31..26,343.20 rows=180,593 width=53) (actual time=122.324..225.961 rows=144,628 loops=3)

  • Hash Cond: (debts.debt_id = dtdca.debt_id)
12. 113.532 113.532 ↑ 1.2 144,628 3

Parallel Seq Scan on debts (cost=0.00..10,701.90 rows=180,593 width=37) (actual time=0.044..37.844 rows=144,628 loops=3)

  • Filter: ((finally_settled_at IS NULL) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 4084
13. 44.970 365.952 ↓ 1.4 66,981 3

Hash (cost=9,140.73..9,140.73 rows=46,446 width=48) (actual time=121.984..121.984 rows=66,981 loops=3)

  • Buckets: 65536 Batches: 2 Memory Usage: 2265kB
14. 27.090 320.982 ↓ 1.4 66,981 3

Subquery Scan on dtdca (cost=0.42..9,140.73 rows=46,446 width=48) (actual time=0.038..106.994 rows=66,981 loops=3)

15. 144.492 293.892 ↓ 1.4 66,981 3

GroupAggregate (cost=0.42..8,676.27 rows=46,446 width=48) (actual time=0.038..97.964 rows=66,981 loops=3)

  • Group Key: debtor_transactions_debts_creditor_agreement.debt_id
16. 149.400 149.400 ↑ 1.0 99,966 3

Index Scan using debtor_transactions_debts_creditor_agreement_debt_id_index on debtor_transactions_debts_creditor_agreement (cost=0.42..7,595.87 rows=99,966 width=24) (actual time=0.026..49.800 rows=99,966 loops=3)

17. 2.907 2.907 ↑ 75.0 11 1

Index Scan using updates_case_id_index on updates (cost=0.42..43,095.77 rows=825 width=35) (actual time=0.128..2.907 rows=11 loops=1)

  • Filter: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
  • Rows Removed by Filter: 832
18. 0.445 0.445 ↑ 423.7 327 1

Index Scan using debtors_on_cases_case_id_index on debtors_on_cases (cost=0.42..10,522.38 rows=138,538 width=20) (actual time=0.009..0.445 rows=327 loops=1)

19. 0.007 0.018 ↑ 1.0 50 1

Hash (cost=2.50..2.50 rows=50 width=55) (actual time=0.018..0.018 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
20. 0.011 0.011 ↑ 1.0 50 1

Seq Scan on creditors (cost=0.00..2.50 rows=50 width=55) (actual time=0.005..0.011 rows=50 loops=1)

21.          

SubPlan (for Hash Left Join)

22. 0.040 0.180 ↑ 1.0 1 10

Aggregate (cost=25.37..25.39 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=10)

23. 0.010 0.140 ↑ 1.0 1 10

Nested Loop Left Join (cost=0.85..25.37 rows=1 width=54) (actual time=0.014..0.014 rows=1 loops=10)

24. 0.030 0.030 ↑ 1.0 1 10

Index Scan using debtors_on_cases_case_id_index on debtors_on_cases debtors_on_cases_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (case_id = cases.case_id)
25. 0.070 0.100 ↑ 1.0 1 10

Index Scan using properties_datas_handle_ref_id_index on properties_datas properties_datas_1 (cost=0.43..16.92 rows=1 width=78) (actual time=0.010..0.010 rows=1 loops=10)

  • Index Cond: ((ref_id = debtors_on_cases_1.debtor_id) AND (handle = 'name'::text))
  • Filter: ((ref = 'debtor'::text) AND (created_at = (SubPlan 1)))
26.          

SubPlan (for Index Scan)

27. 0.000 0.030 ↑ 1.0 1 10

Aggregate (cost=8.45..8.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)

28. 0.030 0.030 ↑ 1.0 1 10

Index Scan using properties_datas_handle_ref_id_index on properties_datas (cost=0.43..8.45 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=10)

  • Index Cond: ((ref_id = debtors_on_cases_1.debtor_id) AND (handle = 'name'::text))
  • Filter: (ref = 'debtor'::text)
Planning time : 3.329 ms
Execution time : 330.817 ms