explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Xz : Getting count of distinct elements, per group, in PostgreSQL - Function based skip scan + aggregate

Settings
# exclusive inclusive rows x rows loops node
1. 0.196 38.655 ↓ 4.7 946 1

Sort (cost=68.64..69.14 rows=200 width=23) (actual time=38.612..38.655 rows=946 loops=1)

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

CTE x

3. 0.508 37.623 ↓ 4.7 946 1

HashAggregate (cost=15.25..17.25 rows=200 width=4) (actual time=37.491..37.623 rows=946 loops=1)

  • Group Key: get_list_of_unique_pairs.dashboard_id
4. 37.115 37.115 ↓ 1.7 1,700 1

Function Scan on get_list_of_unique_pairs (cost=0.25..10.25 rows=1,000 width=4) (actual time=37.014..37.115 rows=1,700 loops=1)

5. 0.276 38.459 ↓ 4.7 946 1

Hash Join (cost=37.00..43.75 rows=200 width=23) (actual time=37.837..38.459 rows=946 loops=1)

  • Hash Cond: (x.dashboard_id = d.id)
6. 37.848 37.848 ↓ 4.7 946 1

CTE Scan on x (cost=0.00..4.00 rows=200 width=12) (actual time=37.493..37.848 rows=946 loops=1)

7. 0.183 0.335 ↑ 1.0 1,200 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
8. 0.152 0.152 ↑ 1.0 1,200 1

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

Total runtime : 38.746 ms