explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kQ4s

Settings
# exclusive inclusive rows x rows loops node
1. 1,090.540 1,561,910.690 ↓ 1.1 408,307 1

Sort (cost=26,231,203,999.40..26,231,204,916.32 rows=366,768 width=4) (actual time=1,561,770.996..1,561,910.690 rows=408,307 loops=1)

  • Sort Key: ((((SubPlan 1) - iplc_case.filed_on) - ((SubPlan 2) - iplc_case.filed_on)))
  • Sort Method: quicksort Memory: 31428kB
2. 2,955.349 1,560,820.150 ↓ 1.1 408,307 1

Nested Loop Anti Join (cost=125,343.33..26,231,170,101.77 rows=366,768 width=4) (actual time=1,804.840..1,560,820.150 rows=408,307 loops=1)

3. 2,609.035 1,287,987.880 ↓ 1.1 425,755 1

Nested Loop (cost=125,342.90..22,181,127,370.20 rows=377,731 width=8) (actual time=1,802.549..1,287,987.880 rows=425,755 loops=1)

4. 3,246.824 4,026.183 ↓ 1.4 1,395,809 1

HashAggregate (cost=125,342.47..135,386.21 rows=1,004,374 width=4) (actual time=1,766.049..4,026.183 rows=1,395,809 loops=1)

  • Group Key: positve_case_tags.case_id
5. 665.351 779.359 ↓ 1.0 1,444,515 1

Bitmap Heap Scan on positve_case_tags (cost=31,671.38..121,766.28 rows=1,430,476 width=4) (actual time=123.249..779.359 rows=1,444,515 loops=1)

  • Recheck Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
  • Heap Blocks: exact=39891
6. 114.008 114.008 ↓ 1.0 1,445,651 1

Bitmap Index Scan on ix_positve_case_tags_tag_id (cost=0.00..31,313.76 rows=1,430,476 width=0) (actual time=114.008..114.008 rows=1,445,651 loops=1)

  • Index Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
7. 68,170.105 1,281,352.662 ↓ 0.0 0 1,395,809

Index Only Scan using iplc_case_id_terminated_on_filed_on_court_id_idx on iplc_case (cost=0.43..22,084.39 rows=1 width=8) (actual time=0.918..0.918 rows=0 loops=1,395,809)

  • Index Cond: ((id = positve_case_tags.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: 570188
8.          

SubPlan (forIndex Only Scan)

9. 1,375.904 661,809.824 ↑ 1.0 1 1,375,904

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.481..0.481 rows=1 loops=1,375,904)

10. 10,637.068 660,433.920 ↑ 17.0 1 1,375,904

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.302..0.480 rows=1 loops=1,375,904)

11. 338,472.384 338,472.384 ↑ 21.8 32 1,375,904

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.050..0.246 rows=32 loops=1,375,904)

  • Index Cond: (case_id = iplc_case.id)
12. 311,324.468 311,324.468 ↓ 0.0 0 44,474,924

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.007..0.007 rows=0 loops=44,474,924)

  • Index Cond: ((docket_entry_id = docket_entry_2.id) AND (tag_id = 15))
  • Heap Fetches: 32404
13. 1,375.904 265,549.472 ↑ 1.0 1 1,375,904

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.193..0.193 rows=1 loops=1,375,904)

14. 24,358.192 264,173.568 ↑ 21.0 1 1,375,904

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.084..0.192 rows=1 loops=1,375,904)

15. 61,915.680 61,915.680 ↑ 21.8 32 1,375,904

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.010..0.045 rows=32 loops=1,375,904)

  • Index Cond: (case_id = iplc_case.id)
16. 177,899.696 177,899.696 ↓ 0.0 0 44,474,924

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.004..0.004 rows=0 loops=44,474,924)

  • Index Cond: ((docket_entry_id = docket_entry_3.id) AND (tag_id = 257))
  • Heap Fetches: 32722
17. 1,363.227 146,319.698 ↑ 1.0 1 454,409

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.322..0.322 rows=1 loops=454,409)

18. 4,586.960 144,956.471 ↑ 8.5 2 454,409

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.112..0.319 rows=2 loops=454,409)

19. 30,445.403 30,445.403 ↑ 11.6 60 454,409

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.009..0.067 rows=60 loops=454,409)

  • Index Cond: (case_id = iplc_case.id)
20. 109,924.108 109,924.108 ↓ 0.0 0 27,481,027

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.004..0.004 rows=0 loops=27,481,027)

  • Index Cond: ((docket_entry_id = docket_entry_4.id) AND (tag_id = 15))
  • Heap Fetches: 29598
21. 908.818 139,503.563 ↑ 1.0 1 454,409

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.307..0.307 rows=1 loops=454,409)

22. 0.000 138,594.745 ↑ 21.0 1 454,409

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.095..0.305 rows=1 loops=454,409)

23. 29,082.176 29,082.176 ↑ 11.6 60 454,409

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.010..0.064 rows=60 loops=454,409)

  • Index Cond: (case_id = iplc_case.id)
24. 109,924.108 109,924.108 ↓ 0.0 0 27,481,027

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.004..0.004 rows=0 loops=27,481,027)

  • Index Cond: ((docket_entry_id = docket_entry_5.id) AND (tag_id = 257))
  • Heap Fetches: 7917
25. 19,584.730 19,584.730 ↓ 0.0 0 425,755

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.046..0.046 rows=0 loops=425,755)

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

SubPlan (forNested Loop Anti Join)

27. 816.614 127,391.784 ↑ 1.0 1 408,307

Aggregate (cost=5,520.86..5,520.87 rows=1 width=4) (actual time=0.312..0.312 rows=1 loops=408,307)

28. 1,242.280 126,575.170 ↑ 8.5 2 408,307

Nested Loop (cost=1.13..5,520.82 rows=17 width=4) (actual time=0.110..0.310 rows=2 loops=408,307)

29. 26,948.262 26,948.262 ↑ 11.6 60 408,307

Index Scan using ix_docket_entry_case_id on docket_entry (cost=0.57..2,296.49 rows=698 width=8) (actual time=0.010..0.066 rows=60 loops=408,307)

  • Index Cond: (case_id = iplc_case.id)
30. 98,384.628 98,384.628 ↓ 0.0 0 24,596,157

Index Only Scan using positve_docket_tags_pkey on positve_docket_tags (cost=0.56..4.62 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=24,596,157)

  • Index Cond: ((docket_entry_id = docket_entry.id) AND (tag_id = 15))
  • Heap Fetches: 26927
31. 816.614 122,900.407 ↑ 1.0 1 408,307

Aggregate (cost=5,520.87..5,520.88 rows=1 width=4) (actual time=0.301..0.301 rows=1 loops=408,307)

32. 0.000 122,083.793 ↑ 21.0 1 408,307

Nested Loop (cost=1.13..5,520.82 rows=21 width=4) (actual time=0.091..0.299 rows=1 loops=408,307)

33. 25,723.341 25,723.341 ↑ 11.6 60 408,307

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.009..0.063 rows=60 loops=408,307)

  • Index Cond: (case_id = iplc_case.id)
34. 98,384.628 98,384.628 ↓ 0.0 0 24,596,157

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.004..0.004 rows=0 loops=24,596,157)

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