explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YwlG : Getting count of distinct elements, per group, in PostgreSQL - Naive query

Settings
# exclusive inclusive rows x rows loops node
1. 0.989 492,506.274 ↑ 1.3 946 1

Sort (cost=3,364,746.61..3,364,749.61 rows=1,200 width=19) (actual time=492,506.208..492,506.274 rows=946 loops=1)

  • Sort Key: (count(DISTINCT time_on_site_logs.user_id))
  • Sort Method: quicksort Memory: 98kB
2. 2,819.967 492,505.285 ↑ 1.3 946 1

GroupAggregate (cost=3,259,664.23..3,364,685.23 rows=1,200 width=19) (actual time=432,234.320..492,505.285 rows=946 loops=1)

  • Group Key: dashboards.name
3. 483,112.803 489,685.318 ↑ 1.0 14,001,200 1

Sort (cost=3,259,664.23..3,294,667.23 rows=14,001,200 width=19) (actual time=432,201.857..489,685.318 rows=14,001,200 loops=1)

  • Sort Key: dashboards.name
  • Sort Method: external merge Disk: 410624kB
4. 4,799.433 6,572.515 ↑ 1.0 14,001,200 1

Hash Join (cost=37.00..449,242.50 rows=14,001,200 width=19) (actual time=0.534..6,572.515 rows=14,001,200 loops=1)

  • Hash Cond: (time_on_site_logs.dashboard_id = dashboards.id)
5. 1,772.707 1,772.707 ↑ 1.0 14,001,200 1

Seq Scan on time_on_site_logs (cost=0.00..256,689.00 rows=14,001,200 width=8) (actual time=0.143..1,772.707 rows=14,001,200 loops=1)

6. 0.206 0.375 ↑ 1.0 1,200 1

Hash (cost=22.00..22.00 rows=1,200 width=19) (actual time=0.375..0.375 rows=1,200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
7. 0.169 0.169 ↑ 1.0 1,200 1

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

Total runtime : 492,561.048 ms