explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SlVi

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 335.134 ↑ 1.0 10 1

Limit (cost=51,675.07..51,935.20 rows=10 width=176) (actual time=330.451..335.134 rows=10 loops=1)

2. 0.524 335.132 ↑ 9,444.0 10 1

Hash Left Join (cost=51,675.07..2,508,328.02 rows=94,440 width=176) (actual time=330.450..335.132 rows=10 loops=1)

  • Hash Cond: (cases.creditor_id = creditors.creditor_id)
  • Filter: ((hashed SubPlan 3) 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.115 333.289 ↑ 423.7 327 1

Merge Left Join (cost=49,285.98..101,867.14 rows=138,538 width=141) (actual time=328.893..333.289 rows=327 loops=1)

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

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

  • Merge Cond: (cases.case_id = debts.case_id)
5. 0.247 0.247 ↑ 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.247 rows=326 loops=1)

6. 0.533 332.497 ↑ 234.8 325 1

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

  • Group Key: debts.case_id
7. 0.000 331.964 ↑ 270.2 565 1

Gather Merge (cost=49,285.14..69,855.06 rows=152,646 width=80) (actual time=328.858..331.964 rows=565 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 1.677 909.504 ↑ 144.0 530 3

Partial GroupAggregate (cost=48,285.12..51,235.90 rows=76,323 width=80) (actual time=302.511..303.168 rows=530 loops=3)

  • Group Key: debts.case_id
9. 238.398 907.827 ↑ 219.2 824 3

Sort (cost=48,285.12..48,736.60 rows=180,593 width=53) (actual time=302.502..302.609 rows=824 loops=3)

  • Sort Key: debts.case_id
  • Sort Method: external merge Disk: 8816kB
10. 187.650 669.429 ↑ 1.2 144,628 3

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

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

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

  • Filter: ((finally_settled_at IS NULL) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 4084
12. 33.216 348.438 ↓ 1.4 66,981 3

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2265kB
13. 15.027 315.222 ↓ 1.4 66,981 3

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

14. 150.303 300.195 ↓ 1.4 66,981 3

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

  • Group Key: debtor_transactions_debts_creditor_agreement.debt_id
15. 149.892 149.892 ↑ 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.011..49.964 rows=99,966 loops=3)

16. 0.304 0.304 ↑ 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.014..0.304 rows=327 loops=1)

17. 0.008 0.019 ↑ 1.0 50 1

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

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

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

19.          

SubPlan (for Hash Left Join)

20. 1.035 1.150 ↓ 0.0 0 1

Bitmap Heap Scan on updates (cost=26.65..2,385.55 rows=165 width=16) (actual time=1.150..1.150 rows=0 loops=1)

  • Recheck Cond: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
  • Filter: (lower(update) ~~ '%em%'::text)
  • Rows Removed by Filter: 3882
  • Heap Blocks: exact=53
21. 0.115 0.115 ↓ 4.7 3,882 1

Bitmap Index Scan on updates_update_type_id_case_id_composite_index (cost=0.00..26.61 rows=825 width=0) (actual time=0.115..0.115 rows=3,882 loops=1)

  • Index Cond: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
22. 0.030 0.150 ↑ 1.0 1 10

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

23. 0.010 0.120 ↑ 1.0 1 10

Nested Loop Left Join (cost=0.85..25.37 rows=1 width=54) (actual time=0.012..0.012 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.002..0.003 rows=1 loops=10)

  • Index Cond: (case_id = cases.case_id)
25. 0.050 0.080 ↑ 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.008..0.008 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.010 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.020 0.020 ↑ 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.002 rows=1 loops=10)

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