explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aM2h

Settings
# exclusive inclusive rows x rows loops node
1. 234.855 881.994 ↓ 46.6 72,681 1

Limit (cost=943,486.18..947,947.32 rows=1,560 width=54) (actual time=445.130..881.994 rows=72,681 loops=1)

  • Buffers: shared hit=407350
  • JIT:
  • Functions: 27
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.390 ms, Inlining 15.165 ms, Optimization 141.136 ms, Emission 70.203 ms, Total 229.894 ms
2.          

CTE z_filtered_edge

3. 96.115 96.115 ↓ 1.2 42,212 1

Index Scan using treenode_edge_z_range_gist on treenode_edge te (cost=0.42..41,288.96 rows=35,642 width=80) (actual time=0.281..96.115 rows=42,212 loops=1)

  • Index Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), '[]'::text) && '[160920,160960)'::floatrange)
  • Filter: (project_id = 1)
  • Rows Removed by Filter: 1270
  • Buffers: shared hit=43945
4.          

CTE bb_edge

5. 159.625 159.625 ↓ 42,212.0 42,212 1

CTE Scan on z_filtered_edge e (cost=0.00..902,099.02 rows=1 width=16) (actual time=0.299..159.625 rows=42,212 loops=1)

  • Filter: ((edge && '0103000000010000000500000000000000209007C100000000101014C100000000209007C100000000F8072D4100000000FC71324100000000F8072D4100000000FC71324100000000101014C100000000209007C100000000101014C1'::geometry) AND st_3ddwithin(edge, '0103000080010000000500000000000000209007C100000000101014C10000000060A5034100000000FC71324100000000101014C10000000060A5034100000000FC71324100000000F8072D410000000060A5034100000000209007C100000000F8072D410000000060A5034100000000209007C100000000101014C10000000060A50341'::geometry, '20'::double precision))
  • Buffers: shared hit=43945
6. 45.795 647.139 ↓ 46.6 72,681 1

Nested Loop (cost=98.20..4,559.34 rows=1,560 width=54) (actual time=218.362..647.139 rows=72,681 loops=1)

  • Buffers: shared hit=407350
7. 51.199 237.934 ↓ 46.6 72,682 1

HashAggregate (cost=97.64..144.44 rows=1,560 width=8) (actual time=218.311..237.934 rows=72,682 loops=1)

  • Group Key: bb_edge.id
  • Buffers: shared hit=43945
8. 8.297 186.735 ↓ 55.1 85,982 1

Append (cost=0.00..93.74 rows=1,560 width=8) (actual time=0.305..186.735 rows=85,982 loops=1)

  • Buffers: shared hit=43945
9. 172.438 172.438 ↓ 42,212.0 42,212 1

CTE Scan on bb_edge (cost=0.00..0.06 rows=1 width=8) (actual time=0.304..172.438 rows=42,212 loops=1)

  • Buffers: shared hit=43945
10. 5.819 5.819 ↓ 42,212.0 42,212 1

CTE Scan on bb_edge bb_edge_1 (cost=0.00..0.06 rows=1 width=8) (actual time=0.016..5.819 rows=42,212 loops=1)

11. 0.168 0.181 ↑ 1.0 1,558 1

ProjectSet (cost=0.00..23.42 rows=1,558 width=8) (actual time=0.022..0.181 rows=1,558 loops=1)

12. 0.013 0.013 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)

13. 363.410 363.410 ↑ 1.0 1 72,682

Index Scan using treenode_id_pkey on treenode t1 (cost=0.56..2.80 rows=1 width=54) (actual time=0.005..0.005 rows=1 loops=72,682)

  • Index Cond: (id = bb_edge.id)
  • Buffers: shared hit=363405
Planning time : 2.399 ms
Execution time : 892.952 ms