explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y65S

Settings
# exclusive inclusive rows x rows loops node
1. 5.556 3,387.880 ↓ 1.3 257 1

GroupAggregate (cost=4,653,002,461.20..4,808,152,901.43 rows=200 width=80) (actual time=3,380.521..3,387.880 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.424 3,382.324 ↑ 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,380.465..3,382.324 rows=29,067 loops=1)

  • Sort Key: a.studentid, a.groupby
  • Sort Method: quicksort Memory: 2,131kB
3. 128.694 3,375.900 ↑ 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,754.019..3,375.900 rows=29,067 loops=1)

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

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

5. 1,208.270 2,339.253 ↑ 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,117.802..2,339.253 rows=1,924,229 loops=1)

  • Sort Key: va.studentid, va.date
  • Sort Method: external sort Disk: 41,496kB
6. 192.467 1,130.983 ↑ 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=305.851..1,130.983 rows=1,928,969 loops=1)

7. 0.020 0.020 ↑ 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.020..0.020 rows=1 loops=1)

8. 326.116 938.496 ↓ 1.0 1,928,969 1

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

9. 310.585 612.380 ↓ 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=305.827..612.380 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=23,267
10. 301.795 301.795 ↓ 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=301.795..301.795 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.517 636.571 ↑ 122.9 29,217 1

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

12. 0.987 635.054 ↑ 1,618.0 2,220 1

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

  • Sort Key: a.studentid
  • Sort Method: quicksort Memory: 201kB
13. 114.562 634.067 ↑ 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=520.147..634.067 rows=2,220 loops=1)

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

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

15. 20.393 82.237 ↑ 1,124.0 62,057 1

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

  • Buckets: 131,072 Batches: 1,024 Memory Usage: 1,026kB
16. 5.817 61.844 ↑ 1,119.5 62,308 1

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

17. 0.015 0.015 ↑ 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.014..0.015 rows=1 loops=1)

18. 8.813 56.012 ↑ 1.1 62,308 1

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

19. 47.199 47.199 ↑ 1.1 62,308 1

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

  • Filter: ((date >= '2018-08-06'::date) AND (date <= '2019-08-04'::date) AND (accountid = 1))
  • Rows Removed by Filter: 154,852