explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EFur

Settings
# exclusive inclusive rows x rows loops node
1. 5.771 3,313.194 ↓ 1.3 257 1

GroupAggregate (cost=4,653,002,461.20..4,808,152,901.43 rows=200 width=80) (actual time=3,305.625..3,313.194 rows=257 loops=1)

  • Group Key: a.studentid, a.groupby
  • Filter: ((count(DISTINCT va.date))::numeric > 16.1500000000000000)
  • Rows Removed by Filter: 950
2. 6.355 3,307.423 ↑ 427,014.7 29,067 1

Sort (cost=4,653,002,461.20..4,684,032,548.85 rows=12,412,035,058 width=44) (actual time=3,305.575..3,307.423 rows=29,067 loops=1)

  • Sort Key: a.studentid, a.groupby
  • Sort Method: quicksort Memory: 2131kB
3. 124.458 3,301.068 ↑ 427,014.7 29,067 1

Merge Join (cost=462,439,203.98..662,983,783.51 rows=12,412,035,058 width=44) (actual time=2,693.646..3,301.068 rows=29,067 loops=1)

  • Merge Cond: (va.studentid = a.studentid)
4. 265.320 2,563.991 ↓ 2.8 1,924,229 1

Unique (cost=459,066,068.02..473,412,503.02 rows=691,100 width=12) (actual time=2,081.308..2,563.991 rows=1,924,229 loops=1)

5. 1,211.944 2,298.671 ↑ 994.1 1,924,229 1

Sort (cost=459,066,068.02..463,848,213.02 rows=1,912,858,000 width=12) (actual time=2,081.308..2,298.671 rows=1,924,229 loops=1)

  • Sort Key: va.studentid, va.date
  • Sort Method: external sort Disk: 41496kB
6. 170.233 1,086.727 ↑ 991.6 1,928,969 1

Nested Loop (cost=54,937.66..33,407,746.11 rows=1,912,858,000 width=12) (actual time=298.903..1,086.727 rows=1,928,969 loops=1)

7. 0.015 0.015 ↑ 1,000.0 1 1

Function Scan on regexp_split_to_table c_1 (cost=0.00..10.00 rows=1,000 width=0) (actual time=0.015..0.015 rows=1 loops=1)

8. 329.103 916.479 ↓ 1.0 1,928,969 1

Materialize (cost=54,937.66..170,134.26 rows=1,912,858 width=12) (actual time=298.888..916.479 rows=1,928,969 loops=1)

9. 292.520 587.376 ↓ 1.0 1,928,969 1

Bitmap Heap Scan on view_attendanceevents va (cost=54,937.66..151,228.97 rows=1,912,858 width=12) (actual time=298.885..587.376 rows=1,928,969 loops=1)

  • Recheck Cond: ((accountid = 1) AND (date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date) AND (date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date))
  • Heap Blocks: exact=23267
10. 294.856 294.856 ↓ 1.0 1,928,969 1

Bitmap Index Scan on view_attendance_idx_accountid_date (cost=0.00..54,459.45 rows=1,912,858 width=0) (actual time=294.856..294.856 rows=1,928,969 loops=1)

  • Index Cond: ((accountid = 1) AND (date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date) AND (date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date))
11. 1.346 612.619 ↑ 122.9 29,217 1

Materialize (cost=3,373,135.96..3,391,095.79 rows=3,591,965 width=40) (actual time=611.052..612.619 rows=29,217 loops=1)

12. 1.061 611.273 ↑ 1,618.0 2,220 1

Sort (cost=3,373,135.96..3,382,115.87 rows=3,591,965 width=40) (actual time=611.049..611.273 rows=2,220 loops=1)

  • Sort Key: a.studentid
  • Sort Method: quicksort Memory: 201kB
13. 115.790 610.212 ↑ 1,618.0 2,220 1

Hash Left Join (cost=2,041,909.52..2,785,595.67 rows=3,591,965 width=40) (actual time=495.137..610.212 rows=2,220 loops=1)

  • Hash Cond: (a.studentid = dc.studentid)
14. 411.628 411.628 ↓ 1.3 1,331 1

Function Scan on ufn_indicators_corefilter a (cost=0.25..10.25 rows=1,000 width=40) (actual time=411.176..411.628 rows=1,331 loops=1)

15. 20.689 82.794 ↑ 1,124.0 62,057 1

Hash (cost=897,523.77..897,523.77 rows=69,753,000 width=8) (actual time=82.794..82.794 rows=62,057 loops=1)

  • Buckets: 131072 Batches: 1024 Memory Usage: 1026kB
16. 5.914 62.105 ↑ 1,119.5 62,308 1

Nested Loop (cost=0.00..897,523.77 rows=69,753,000 width=8) (actual time=2.043..62.105 rows=62,308 loops=1)

17. 0.014 0.014 ↑ 1,000.0 1 1

Function Scan on regexp_split_to_table c (cost=0.00..10.00 rows=1,000 width=0) (actual time=0.013..0.014 rows=1 loops=1)

18. 8.807 56.177 ↑ 1.1 62,308 1

Materialize (cost=0.00..25,775.65 rows=69,753 width=8) (actual time=2.029..56.177 rows=62,308 loops=1)

19. 47.370 47.370 ↑ 1.1 62,308 1

Seq Scan on discipline dc (cost=0.00..25,426.89 rows=69,753 width=8) (actual time=2.028..47.370 rows=62,308 loops=1)

  • Filter: ((date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date) AND (accountid = 1))
  • Rows Removed by Filter: 154852
Planning time : 0.394 ms