explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5DK : Getting count of distinct elements, per group, in PostgreSQL - Recursive CTE for the win

Settings
# exclusive inclusive rows x rows loops node
1. 0.229 22.734 ↓ 9.5 946 1

Sort (cost=323.06..323.31 rows=100 width=23) (actual time=22.688..22.734 rows=946 loops=1)

  • Sort Key: uc.count
  • Sort Method: quicksort Memory: 98kB
2.          

CTE distinct_pairs

3. 0.814 19.538 ↓ 16.8 1,701 1

Recursive Union (cost=0.43..285.47 rows=101 width=68) (actual time=0.014..19.538 rows=1,701 loops=1)

4. 0.000 0.013 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.43..2.78 rows=1 width=68) (actual time=0.012..0.013 rows=1 loops=1)

5. 0.002 0.013 ↑ 1.0 1 1

Limit (cost=0.43..2.77 rows=1 width=68) (actual time=0.012..0.013 rows=1 loops=1)

6. 0.011 0.011 ↑ 14,001,200.0 1 1

Index Scan using i3 on time_on_site_logs l_1 (cost=0.43..32,751,722.50 rows=14,001,200 width=68) (actual time=0.011..0.011 rows=1 loops=1)

7. 1.711 18.711 ↑ 10.0 1 1,701

WorkTable Scan on distinct_pairs p (cost=0.00..28.07 rows=10 width=32) (actual time=0.011..0.011 rows=1 loops=1,701)

  • Filter: ((rl).id IS NOT NULL)
  • Rows Removed by Filter: 0
8.          

SubPlan (for WorkTable Scan)

9. 1.700 17.000 ↑ 1.0 1 1,700

Limit (cost=0.44..2.79 rows=1 width=68) (actual time=0.009..0.010 rows=1 loops=1,700)

10. 15.300 15.300 ↑ 4,667,067.0 1 1,700

Index Scan using i3 on time_on_site_logs l (cost=0.44..10,964,145.80 rows=4,667,067 width=68) (actual time=0.009..0.009 rows=1 loops=1,700)

  • Index Cond: (ROW(dashboard_id, user_id) > ROW((p.rl).dashboard_id, (p.rl).user_id))
11.          

CTE unpacked_counts

12. 0.993 21.665 ↓ 9.5 946 1

HashAggregate (cost=2.52..3.52 rows=100 width=32) (actual time=21.492..21.665 rows=946 loops=1)

  • Group Key: (distinct_pairs.rl).dashboard_id
13. 20.672 20.672 ↓ 17.0 1,700 1

CTE Scan on distinct_pairs (cost=0.00..2.02 rows=100 width=32) (actual time=0.016..20.672 rows=1,700 loops=1)

  • Filter: ((rl).dashboard_id IS NOT NULL)
  • Rows Removed by Filter: 1
14. 0.293 22.505 ↓ 9.5 946 1

Hash Join (cost=3.25..30.75 rows=100 width=23) (actual time=22.102..22.505 rows=946 loops=1)

  • Hash Cond: (d.id = uc.dashboard_id)
15. 0.127 0.127 ↑ 1.0 1,200 1

Seq Scan on dashboards d (cost=0.00..22.00 rows=1,200 width=19) (actual time=0.004..0.127 rows=1,200 loops=1)

16. 0.161 22.085 ↓ 9.5 946 1

Hash (cost=2.00..2.00 rows=100 width=12) (actual time=22.085..22.085 rows=946 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
17. 21.924 21.924 ↓ 9.5 946 1

CTE Scan on unpacked_counts uc (cost=0.00..2.00 rows=100 width=12) (actual time=21.495..21.924 rows=946 loops=1)

Total runtime : 22.883 ms