explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fVI3

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

Limit (cost=49,907.29..50,166.85 rows=10 width=176) (actual time=336.834..348.486 rows=10 loops=1)

2. 1.945 348.483 ↑ 10,270.0 10 1

Hash Join (cost=49,907.29..2,715,608.40 rows=102,700 width=176) (actual time=336.833..348.483 rows=10 loops=1)

  • Hash Cond: (cases.creditor_id = creditors.creditor_id)
  • Join Filter: ((hashed SubPlan 3) OR (hashed SubPlan 4) OR (lower(creditors.name) ~~ '%emil%'::text) OR ((cases.id)::text ~~ 'emil%'::text) OR ((debtors_on_cases.fi_payment_id_raw)::text ~~ 'emil%'::text) OR (lower(cases.creditor_reference_id) ~~ '%emil%'::text))
  • Rows Removed by Join Filter: 1286
3. 0.417 341.101 ↑ 106.9 1,296 1

Merge Left Join (cost=49,285.98..101,867.14 rows=138,538 width=141) (actual time=330.332..341.101 rows=1,296 loops=1)

  • Merge Cond: (cases.case_id = debtors_on_cases.case_id)
4. 0.461 339.564 ↑ 106.7 1,292 1

Merge Left Join (cost=49,285.56..89,268.54 rows=137,799 width=137) (actual time=330.315..339.564 rows=1,292 loops=1)

  • Merge Cond: (cases.case_id = debts.case_id)
5. 0.787 0.787 ↑ 106.7 1,292 1

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

6. 2.022 338.316 ↑ 59.1 1,291 1

Finalize GroupAggregate (cost=49,285.14..72,907.98 rows=76,323 width=80) (actual time=330.304..338.316 rows=1,291 loops=1)

  • Group Key: debts.case_id
7. 0.000 336.294 ↑ 66.5 2,295 1

Gather Merge (cost=49,285.14..69,855.06 rows=152,646 width=80) (actual time=330.272..336.294 rows=2,295 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 3.594 932.712 ↑ 68.8 1,109 3

Partial GroupAggregate (cost=48,285.12..51,235.90 rows=76,323 width=80) (actual time=308.681..310.904 rows=1,109 loops=3)

  • Group Key: debts.case_id
9. 264.483 929.118 ↑ 94.0 1,921 3

Sort (cost=48,285.12..48,736.60 rows=180,593 width=53) (actual time=308.671..309.706 rows=1,921 loops=3)

  • Sort Key: debts.case_id
  • Sort Method: external merge Disk: 6280kB
10. 193.056 664.635 ↑ 1.2 144,628 3

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

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

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

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

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

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

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

14. 99.528 255.426 ↓ 1.4 66,981 3

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

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

16. 1.120 1.120 ↑ 106.9 1,296 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.015..1.120 rows=1,296 loops=1)

17. 0.012 0.064 ↓ 1.0 50 1

Hash (cost=2.88..2.88 rows=49 width=39) (actual time=0.064..0.064 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
18. 0.052 0.052 ↓ 1.0 50 1

Seq Scan on creditors (cost=0.00..2.88 rows=49 width=39) (actual time=0.045..0.052 rows=50 loops=1)

  • Filter: (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[]))
19.          

SubPlan (for Hash Join)

20. 0.001 0.171 ↓ 0.0 0 1

Bitmap Heap Scan on updates (cost=82.20..109.66 rows=7 width=16) (actual time=0.171..0.171 rows=0 loops=1)

  • Recheck Cond: ((update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid) AND (lower(update) ~~ '%emil%'::text))
21. 0.002 0.170 ↓ 0.0 0 1

BitmapAnd (cost=82.20..82.20 rows=7 width=0) (actual time=0.170..0.170 rows=0 loops=1)

22. 0.126 0.126 ↓ 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.126..0.126 rows=3,882 loops=1)

  • Index Cond: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
23. 0.042 0.042 ↑ 607.3 6 1

Bitmap Index Scan on updates_gin_index_lower_update_idx (cost=0.00..55.33 rows=3,644 width=0) (actual time=0.042..0.042 rows=6 loops=1)

  • Index Cond: (lower(update) ~~ '%emil%'::text)
24. 0.194 5.042 ↓ 242.8 971 1

Nested Loop (cost=57.22..508.13 rows=4 width=16) (actual time=0.355..5.042 rows=971 loops=1)

25. 2.030 2.277 ↓ 214.2 857 1

Bitmap Heap Scan on properties_datas properties_datas_2 (cost=56.80..474.34 rows=4 width=16) (actual time=0.344..2.277 rows=857 loops=1)

  • Recheck Cond: ((lower((data ->> 'text'::text)) ~~ '%emil%'::text) OR (lower((data ->> 'address'::text)) ~~ '%emil%'::text))
  • Rows Removed by Index Recheck: 5
  • Filter: ((ref = 'debtor'::text) AND ((handle = 'name'::text) OR (handle = 'address'::text) OR (handle = 'birthdate'::text) OR (handle = 'cpr'::text) OR (handle = 'cvr'::text) OR (handle = 'phone'::text)))
  • Rows Removed by Filter: 342
  • Heap Blocks: exact=1057
26. 0.001 0.247 ↓ 0.0 0 1

BitmapOr (cost=56.80..56.80 rows=107 width=0) (actual time=0.247..0.247 rows=0 loops=1)

27. 0.217 0.217 ↓ 11.6 1,089 1

Bitmap Index Scan on properties_datas_gin_index_lower_text_idx (cost=0.00..28.71 rows=94 width=0) (actual time=0.217..0.217 rows=1,089 loops=1)

  • Index Cond: (lower((data ->> 'text'::text)) ~~ '%emil%'::text)
28. 0.029 0.029 ↓ 9.0 117 1

Bitmap Index Scan on properties_datas_gin_index_lower_address_idx (cost=0.00..28.09 rows=13 width=0) (actual time=0.029..0.029 rows=117 loops=1)

  • Index Cond: (lower((data ->> 'address'::text)) ~~ '%emil%'::text)
29. 2.571 2.571 ↑ 1.0 1 857

Index Scan using debtors_on_cases_debtor_id_index on debtors_on_cases debtors_on_cases_2 (cost=0.42..8.44 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=857)

  • Index Cond: (debtor_id = properties_datas_2.ref_id)
30. 0.030 0.160 ↑ 1.0 1 10

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

31. 0.010 0.130 ↑ 1.0 1 10

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

32. 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)
33. 0.060 0.090 ↑ 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.009 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)))
34.          

SubPlan (for Index Scan)

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

36. 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 : 5.570 ms
Execution time : 354.612 ms