explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0rO

Settings
# exclusive inclusive rows x rows loops node
1. 5.195 264,029.739 ↑ 1.0 3 1

Subquery Scan on q (cost=70,895.06..70,906.36 rows=3 width=72) (actual time=264,027.787..264,029.739 rows=3 loops=1)

2. 0.010 264,024.544 ↑ 1.0 3 1

Limit (cost=70,895.06..70,895.07 rows=3 width=44) (actual time=264,024.536..264,024.544 rows=3 loops=1)

3. 2.124 264,024.534 ↑ 66.7 3 1

Sort (cost=70,895.06..70,895.56 rows=200 width=44) (actual time=264,024.533..264,024.534 rows=3 loops=1)

  • Sort Key: (count(q_1.cluster_id)) DESC
  • Sort Method: top-N heapsort Memory: 92kB
4. 30,429.710 264,022.410 ↓ 2.9 575 1

GroupAggregate (cost=70,880.03..70,892.48 rows=200 width=44) (actual time=233,583.447..264,022.410 rows=575 loops=1)

  • Group Key: q_1.cluster_id
5. 174.167 233,592.700 ↓ 47.9 47,659 1

Sort (cost=70,880.03..70,882.51 rows=995 width=36) (actual time=233,533.306..233,592.700 rows=47,659 loops=1)

  • Sort Key: q_1.cluster_id
  • Sort Method: external merge Disk: 78168kB
6. 7.577 233,418.533 ↓ 47.9 47,659 1

Subquery Scan on q_1 (cost=70,282.21..70,830.48 rows=995 width=36) (actual time=233,287.744..233,418.533 rows=47,659 loops=1)

  • Filter: (q_1.cluster_id IS NOT NULL)
  • Rows Removed by Filter: 80
7. 194,573.176 233,410.956 ↓ 47.7 47,739 1

WindowAgg (cost=70,282.21..70,820.48 rows=1,000 width=36) (actual time=233,287.742..233,410.956 rows=47,739 loops=1)

8. 12.068 38,837.780 ↓ 47.7 47,739 1

Subquery Scan on q_2 (cost=70,282.21..70,807.98 rows=1,000 width=64) (actual time=37,681.005..38,837.780 rows=47,739 loops=1)

9. 21.111 38,825.712 ↓ 47.7 47,739 1

Result (cost=70,282.21..70,797.98 rows=1,000 width=64) (actual time=37,681.003..38,825.712 rows=47,739 loops=1)

10. 1,273.804 38,804.601 ↓ 47.7 47,739 1

ProjectSet (cost=70,282.21..70,287.98 rows=1,000 width=64) (actual time=37,680.996..38,804.601 rows=47,739 loops=1)

11. 37,466.910 37,530.797 ↑ 1.0 1 1

Aggregate (cost=70,282.21..70,282.47 rows=1 width=64) (actual time=37,530.797..37,530.797 rows=1 loops=1)

12. 63.887 63.887 ↓ 1.2 47,739 1

Index Scan using history_place_stops_history_place_id_idx on history_place_stops hps (cost=0.42..69,890.73 rows=39,147 width=1,664) (actual time=0.028..63.887 rows=47,739 loops=1)

  • Index Cond: (history_place_id = 168436)
  • Filter: (ellipse_95 IS NOT NULL)
  • Rows Removed by Filter: 28271
Planning time : 0.225 ms
Execution time : 264,071.963 ms