explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rpjR

Settings
# exclusive inclusive rows x rows loops node
1. 0.298 643.502 ↓ 1.1 223 1

Sort (cost=33,400,043.63..33,400,044.14 rows=202 width=4) (actual time=643.479..643.502 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.031 643.204 ↓ 1.1 223 1

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

3. 0.520 572.796 ↓ 1.1 235 1

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

4. 1.331 559.777 ↓ 1.2 431 1

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

5. 0.876 5.166 ↑ 1.6 910 1

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

  • Group Key: fjc_judge_case_assignment.case_id
6. 0.137 4.290 ↑ 1.6 910 1

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

7. 0.064 0.064 ↑ 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.063..0.064 rows=1 loops=1)

  • Index Cond: (id = 564)
  • Heap Fetches: 0
8. 4.089 4.089 ↑ 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.043..4.089 rows=910 loops=1)

  • Index Cond: (fjc_judge_id = 564)
  • Heap Fetches: 284
9. 27.599 553.280 ↓ 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.608..0.608 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 290.997 ↑ 1.0 1 651

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

12. 0.000 290.346 ↑ 17.0 1 651

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

13. 152.334 152.334 ↑ 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.041..0.234 rows=55 loops=651)

  • Index Cond: (case_id = iplc_case.id)
14. 142.512 142.512 ↓ 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.004..0.004 rows=0 loops=35,628)

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

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

16. 4.911 93.744 ↑ 21.0 1 651

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

17. 17.577 17.577 ↑ 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.006..0.027 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 70.839 ↑ 1.0 1 463

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

20. 0.000 70.376 ↑ 8.5 2 463

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

21. 10.649 10.649 ↑ 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.023 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 69.450 ↑ 1.0 1 463

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

24. 0.000 68.987 ↑ 21.0 1 463

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

25. 10.649 10.649 ↑ 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.023 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. 12.499 12.499 ↑ 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.029..0.029 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.446 34.565 ↑ 1.0 1 223

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

31. 0.125 34.119 ↑ 8.5 2 223

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.053..0.153 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.446 34.342 ↑ 1.0 1 223

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

35. 0.125 33.896 ↑ 21.0 1 223

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

36. 5.575 5.575 ↑ 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.025 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
Planning time : 3.023 ms
Execution time : 643.830 ms