explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d2RW

Settings
# exclusive inclusive rows x rows loops node
1. 0.335 524.772 ↓ 1.1 223 1

Sort (cost=33,400,043.63..33,400,044.14 rows=202 width=4) (actual time=524.747..524.772 rows=223 loops=1)

  • Sort Key: ((((SubPlan 1) - iplc_case.filed_on) - ((SubPlan 2) - iplc_case.filed_on)))
  • Sort Method: quicksort Memory: 35kB
2. 1.067 524.437 ↓ 1.1 223 1

Nested Loop Anti Join (cost=76.42..33,400,035.90 rows=202 width=4) (actual time=4.037..524.437 rows=223 loops=1)

3. 0.553 452.209 ↓ 1.1 235 1

Nested Loop Semi Join (cost=75.98..31,169,447.02 rows=208 width=8) (actual time=3.625..452.209 rows=235 loops=1)

4. 0.918 444.760 ↓ 1.2 431 1

Nested Loop (cost=75.55..31,169,105.43 rows=373 width=12) (actual time=2.651..444.760 rows=431 loops=1)

5. 0.926 2.492 ↑ 1.6 910 1

HashAggregate (cost=75.12..89.24 rows=1,412 width=4) (actual time=1.795..2.492 rows=910 loops=1)

  • Group Key: fjc_judge_case_assignment.case_id
6. 0.088 1.566 ↑ 1.6 910 1

Nested Loop (cost=0.71..71.58 rows=1,413 width=4) (actual time=0.113..1.566 rows=910 loops=1)

7. 0.060 0.060 ↑ 1.0 1 1

Index Only Scan using fjc_judge_pkey on fjc_judge (cost=0.28..4.29 rows=1 width=4) (actual time=0.059..0.060 rows=1 loops=1)

  • Index Cond: (id = 564)
  • Heap Fetches: 0
8. 1.418 1.418 ↑ 1.6 910 1

Index Only Scan using fjc_judge_case_assignment_fjc_judge_id_case_id_idx on fjc_judge_case_assignment (cost=0.43..53.16 rows=1,413 width=8) (actual time=0.053..1.418 rows=910 loops=1)

  • Index Cond: (fjc_judge_id = 564)
  • Heap Fetches: 284
9. 15.415 441.350 ↓ 0.0 0 910

Index Only Scan using iplc_case_id_terminated_on_filed_on_court_id_idx on iplc_case (cost=0.43..22,089.95 rows=1 width=8) (actual time=0.485..0.485 rows=0 loops=910)

  • Index Cond: ((id = fjc_judge_case_assignment.case_id) AND (filed_on < '2019-04-09'::date))
  • Filter: (((terminated_on IS NULL) OR (terminated_on > '2009-01-01'::date)) AND ((((SubPlan 3) - filed_on) - ((SubPlan 4) - filed_on)) IS NOT NULL) AND ((((SubPlan 5) - filed_on) - ((SubPlan 6) - filed_on)) > 0))
  • Rows Removed by Filter: 1
  • Heap Fetches: 521
10.          

SubPlan (forIndex Only Scan)

11. 0.651 178.374 ↑ 1.0 1 651

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.274..0.274 rows=1 loops=651)

12. 11.598 177.723 ↑ 17.0 1 651

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.125..0.273 rows=1 loops=651)

13. 59.241 59.241 ↑ 12.7 55 651

Index Scan using ix_docket_entry_case_id on docket_entry docket_entry_2 (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.018..0.091 rows=55 loops=651)

  • Index Cond: (case_id = iplc_case.id)
14. 106.884 106.884 ↓ 0.0 0 35,628

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags positve_docket_tags_2 (cost=0.56..4.62 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=35,628)

  • Index Cond: ((docket_entry_id = docket_entry_2.id) AND (tag_id = 15))
  • Heap Fetches: 34
15. 0.651 95.697 ↑ 1.0 1 651

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.147..0.147 rows=1 loops=651)

16. 6.864 95.046 ↑ 21.0 1 651

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.033..0.146 rows=1 loops=651)

17. 16.926 16.926 ↑ 12.7 55 651

Index Scan using ix_docket_entry_case_id on docket_entry docket_entry_3 (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.005..0.026 rows=55 loops=651)

  • Index Cond: (case_id = iplc_case.id)
18. 71.256 71.256 ↓ 0.0 0 35,628

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags positve_docket_tags_3 (cost=0.56..4.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=35,628)

  • Index Cond: ((docket_entry_id = docket_entry_3.id) AND (tag_id = 257))
  • Heap Fetches: 25
19. 0.463 76.395 ↑ 1.0 1 463

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.165..0.165 rows=1 loops=463)

20. 3.330 75.932 ↑ 8.5 2 463

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.042..0.164 rows=2 loops=463)

21. 12.038 12.038 ↑ 10.7 65 463

Index Scan using ix_docket_entry_case_id on docket_entry docket_entry_4 (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.004..0.026 rows=65 loops=463)

  • Index Cond: (case_id = iplc_case.id)
22. 60.564 60.564 ↓ 0.0 0 30,282

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags positve_docket_tags_4 (cost=0.56..4.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=30,282)

  • Index Cond: ((docket_entry_id = docket_entry_4.id) AND (tag_id = 15))
  • Heap Fetches: 34
23. 0.463 75.469 ↑ 1.0 1 463

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.163..0.163 rows=1 loops=463)

24. 2.404 75.006 ↑ 21.0 1 463

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.031..0.162 rows=1 loops=463)

25. 12.038 12.038 ↑ 10.7 65 463

Index Scan using ix_docket_entry_case_id on docket_entry docket_entry_5 (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.004..0.026 rows=65 loops=463)

  • Index Cond: (case_id = iplc_case.id)
26. 60.564 60.564 ↓ 0.0 0 30,282

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags positve_docket_tags_5 (cost=0.56..4.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=30,282)

  • Index Cond: ((docket_entry_id = docket_entry_5.id) AND (tag_id = 257))
  • Heap Fetches: 17
27. 6.896 6.896 ↑ 1.0 1 431

Index Only Scan using positve_case_tags_pkey on positve_case_tags (cost=0.43..0.91 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=431)

  • Index Cond: (case_id = iplc_case.id)
  • Filter: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
  • Rows Removed by Filter: 3
  • Heap Fetches: 265
28. 0.470 0.470 ↓ 0.0 0 235

Index Only Scan using positve_case_tags_pkey on positve_case_tags positve_case_tags_1 (cost=0.43..0.74 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=235)

  • Index Cond: ((case_id = iplc_case.id) AND (tag_id = 359))
  • Heap Fetches: 8
29.          

SubPlan (forNested Loop Anti Join)

30. 0.223 35.457 ↑ 1.0 1 223

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.159..0.159 rows=1 loops=223)

31. 1.240 35.234 ↑ 8.5 2 223

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.052..0.158 rows=2 loops=223)

32. 5.798 5.798 ↑ 11.1 63 223

Index Scan using ix_docket_entry_case_id on docket_entry (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.004..0.026 rows=63 loops=223)

  • Index Cond: (case_id = iplc_case.id)
33. 28.196 28.196 ↓ 0.0 0 14,098

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags (cost=0.56..4.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=14,098)

  • Index Cond: ((docket_entry_id = docket_entry.id) AND (tag_id = 15))
  • Heap Fetches: 14
34. 0.223 35.234 ↑ 1.0 1 223

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.158..0.158 rows=1 loops=223)

35. 0.794 35.011 ↑ 21.0 1 223

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.036..0.157 rows=1 loops=223)

36. 6.021 6.021 ↑ 11.1 63 223

Index Scan using ix_docket_entry_case_id on docket_entry docket_entry_1 (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.004..0.027 rows=63 loops=223)

  • Index Cond: (case_id = iplc_case.id)
37. 28.196 28.196 ↓ 0.0 0 14,098

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags positve_docket_tags_1 (cost=0.56..4.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=14,098)

  • Index Cond: ((docket_entry_id = docket_entry_1.id) AND (tag_id = 257))
  • Heap Fetches: 3