explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Wgn

Settings
# exclusive inclusive rows x rows loops node
1. 0.164 88,801.680 ↑ 13.1 1,531 1

Limit (cost=7,764,667.20..7,801,367.22 rows=20,000 width=140) (actual time=88,801.221..88,801.680 rows=1,531 loops=1)

2. 0.529 88,801.516 ↑ 89.3 1,531 1

WindowAgg (cost=7,764,667.20..8,015,638.49 rows=136,769 width=140) (actual time=88,801.219..88,801.516 rows=1,531 loops=1)

3. 2,228.137 88,800.987 ↑ 89.3 1,531 1

Subquery Scan on inner (cost=7,764,667.20..8,013,928.88 rows=136,769 width=132) (actual time=84,560.258..88,800.987 rows=1,531 loops=1)

  • Filter: (("inner".y_max > 36.99860967447947) AND ("inner".y_min < 37.16130691921545) AND ("inner".x_max > '-122.50030517578126'::numeric) AND ("inner".x_min < '-121.98326110839845'::numeric))
  • Rows Removed by Filter: 11076766
4. 26,916.115 86,572.850 ↑ 1.0 11,078,297 1

Sort (cost=7,764,667.20..7,792,362.94 rows=11,078,297 width=140) (actual time=84,002.908..86,572.850 rows=11,078,297 loops=1)

  • Sort Key: (COALESCE(_percentile_blocks_feature_count_metric_1.block_code)), (COALESCE(_percentile_blocks_feature_count_metric_1.x_min)), (COALESCE(_percentile_blocks_feature_count_metric_1.x_max)), (COALESCE(_percentile_blocks_feature_count_metric_1.y_min)), (COALESCE(_percentile_blocks_feature_count_metric_1.y_max)), _percentile_blocks_feature_count_metric_1._percentile_blocks_feature_count_metric_1
  • Sort Method: external merge Disk: 888864kB
5.          

CTE _percentile_blocks_feature_count_metric_1

6. 9,121.277 20,553.048 ↑ 1.0 11,078,297 1

Sort (cost=3,148,904.00..3,176,599.75 rows=11,078,297 width=132) (actual time=17,969.736..20,553.048 rows=11,078,297 loops=1)

  • Sort Key: block_dwh.block_code, block_dwh.x_min, block_dwh.x_max, block_dwh.y_min, block_dwh.y_max
  • Sort Method: external merge Disk: 802160kB
7. 4,723.069 11,431.771 ↑ 1.0 11,078,297 1

GroupAggregate (cost=0.56..1,095,369.47 rows=11,078,297 width=132) (actual time=0.019..11,431.771 rows=11,078,297 loops=1)

  • Group Key: block_dwh.block_code
8. 6,708.702 6,708.702 ↑ 1.0 11,078,297 1

Index Scan using block_dwh_pkey on block_dwh (cost=0.56..929,195.02 rows=11,078,297 width=124) (actual time=0.011..6,708.702 rows=11,078,297 loops=1)

9. 30,924.133 59,656.735 ↑ 1.0 11,078,297 1

WindowAgg (cost=2,275,100.47..2,496,666.41 rows=11,078,297 width=140) (actual time=31,264.123..59,656.735 rows=11,078,297 loops=1)

10. 4,905.894 28,732.602 ↑ 1.0 11,078,297 1

Sort (cost=2,275,100.47..2,302,796.22 rows=11,078,297 width=132) (actual time=27,381.740..28,732.602 rows=11,078,297 loops=1)

  • Sort Key: _percentile_blocks_feature_count_metric_1._percentile_blocks_feature_count_metric_1 NULLS FIRST
  • Sort Method: external merge Disk: 768536kB
11. 23,826.708 23,826.708 ↑ 1.0 11,078,297 1

CTE Scan on _percentile_blocks_feature_count_metric_1 (cost=0.00..221,565.94 rows=11,078,297 width=132) (actual time=17,969.741..23,826.708 rows=11,078,297 loops=1)

Planning time : 0.177 ms
Execution time : 89,150.007 ms