explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fPfD

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

Limit (cost=104,101.16..104,355.31 rows=10 width=176) (actual time=562.968..563.068 rows=10 loops=1)

2. 0.026 563.066 ↑ 21,382.2 10 1

GroupAggregate (cost=104,101.16..5,538,387.29 rows=213,822 width=176) (actual time=562.967..563.066 rows=10 loops=1)

  • Group Key: cases.case_id, creditors.name
3. 3.686 562.910 ↑ 10,182.0 21 1

Sort (cost=104,101.16..104,635.71 rows=213,822 width=117) (actual time=562.909..562.910 rows=21 loops=1)

  • Sort Key: cases.case_id, creditors.name
  • Sort Method: quicksort Memory: 2481kB
4. 5.649 559.224 ↑ 25.1 8,516 1

Hash Left Join (cost=37,518.50..72,014.94 rows=213,822 width=117) (actual time=393.671..559.224 rows=8,516 loops=1)

  • Hash Cond: (debts.debt_id = dtdca.debt_id)
5. 80.143 481.884 ↑ 25.1 8,516 1

Hash Right Join (cost=27,388.19..52,094.13 rows=213,822 width=101) (actual time=321.789..481.884 rows=8,516 loops=1)

  • Hash Cond: (debts.case_id = cases.case_id)
6. 81.229 81.229 ↓ 1.0 433,884 1

Seq Scan on debts (cost=0.00..13,304.37 rows=433,424 width=37) (actual time=0.058..81.229 rows=433,884 loops=1)

  • Filter: ((finally_settled_at IS NULL) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 12253
7. 1.472 320.512 ↑ 16.2 4,200 1

Hash (cost=25,674.42..25,674.42 rows=67,981 width=80) (actual time=320.512..320.512 rows=4,200 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 676kB
8. 142.732 319.040 ↑ 16.2 4,200 1

Hash Right Join (cost=21,110.01..25,674.42 rows=67,981 width=80) (actual time=177.549..319.040 rows=4,200 loops=1)

  • Hash Cond: (updates.case_id = cases.case_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: 134373
9. 0.458 0.588 ↓ 4.7 3,882 1

Bitmap Heap Scan on updates (cost=26.82..2,381.59 rows=825 width=35) (actual time=0.139..0.588 rows=3,882 loops=1)

  • Recheck Cond: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
  • Heap Blocks: exact=53
10. 0.130 0.130 ↓ 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.130..0.130 rows=3,882 loops=1)

  • Index Cond: (update_type_id = 'cbffe6c7-10cd-4aa1-a656-0e465ecaa544'::uuid)
11. 33.761 175.720 ↓ 1.0 138,539 1

Hash (cost=17,186.47..17,186.47 rows=138,538 width=100) (actual time=175.720..175.720 rows=138,539 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 2683kB
12. 22.095 141.959 ↓ 1.0 138,539 1

Hash Left Join (cost=7,378.60..17,186.47 rows=138,538 width=100) (actual time=49.054..141.959 rows=138,539 loops=1)

  • Hash Cond: (cases.creditor_id = creditors.creditor_id)
13. 56.263 119.844 ↓ 1.0 138,539 1

Hash Right Join (cost=7,375.48..15,458.89 rows=138,538 width=77) (actual time=49.019..119.844 rows=138,539 loops=1)

  • Hash Cond: (debtors_on_cases.case_id = cases.case_id)
14. 14.701 14.701 ↑ 1.0 138,538 1

Seq Scan on debtors_on_cases (cost=0.00..2,960.38 rows=138,538 width=20) (actual time=0.006..14.701 rows=138,538 loops=1)

15. 28.362 48.880 ↑ 1.0 137,799 1

Hash (cost=3,902.99..3,902.99 rows=137,799 width=73) (actual time=48.880..48.880 rows=137,799 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2356kB
16. 20.518 20.518 ↑ 1.0 137,799 1

Seq Scan on cases (cost=0.00..3,902.99 rows=137,799 width=73) (actual time=0.006..20.518 rows=137,799 loops=1)

17. 0.010 0.020 ↑ 1.0 50 1

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

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

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

19. 11.308 71.691 ↓ 1.4 66,981 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2265kB
20. 5.105 60.383 ↓ 1.4 66,981 1

Subquery Scan on dtdca (cost=0.42..9,140.73 rows=46,446 width=48) (actual time=0.015..60.383 rows=66,981 loops=1)

21. 25.744 55.278 ↓ 1.4 66,981 1

GroupAggregate (cost=0.42..8,676.27 rows=46,446 width=48) (actual time=0.015..55.278 rows=66,981 loops=1)

  • Group Key: debtor_transactions_debts_creditor_agreement.debt_id
22. 29.534 29.534 ↑ 1.0 99,966 1

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.009..29.534 rows=99,966 loops=1)

23.          

SubPlan (for GroupAggregate)

24. 0.020 0.130 ↑ 1.0 1 10

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

25. 0.010 0.110 ↑ 1.0 1 10

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

26. 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)
27. 0.050 0.070 ↑ 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.007..0.007 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)))
28.          

SubPlan (for Index Scan)

29. 0.000 0.020 ↑ 1.0 1 10

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

30. 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.858 ms
Execution time : 563.183 ms