explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eP1x

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 136.273 ↑ 71.0 1 1

Unique (cost=8,614,847.90..8,614,849.67 rows=71 width=86) (actual time=136.273..136.273 rows=1 loops=1)

2. 0.014 136.273 ↑ 71.0 1 1

Sort (cost=8,614,847.90..8,614,848.08 rows=71 width=86) (actual time=136.273..136.273 rows=1 loops=1)

  • Sort Key: (0), o.id, (0), (NULL::text), (0), (0), (0), o.name, (CASE WHEN (SubPlan 1) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 25kB
3. 0.007 136.259 ↑ 71.0 1 1

Hash Anti Join (cost=8,614,803.11..8,614,845.72 rows=71 width=86) (actual time=136.258..136.259 rows=1 loops=1)

  • Hash Cond: (o.id = di.partnerorganisationid)
4. 0.009 136.249 ↑ 142.0 1 1

HashAggregate (cost=8,614,781.89..8,614,820.59 rows=142 width=86) (actual time=136.249..136.249 rows=1 loops=1)

  • Group Key: 0, o.id, 0, NULL::text, 0, 0, 0, o.name, CASE WHEN (SubPlan 1) THEN true ELSE false END
5. 6.690 136.240 ↑ 6,777,764.0 4 1

Nested Loop (cost=359.60..8,004,783.13 rows=27,111,056 width=86) (actual time=59.703..136.240 rows=4 loops=1)

6. 0.000 9.838 ↑ 1,359.6 19,940 1

Nested Loop (cost=359.35..345,909.56 rows=27,111,056 width=233) (actual time=1.055..9.838 rows=19,940 loops=1)

7. 1.044 1.044 ↑ 2.8 9,970 1

Seq Scan on loggedinorgareatable (cost=0.00..479.08 rows=27,608 width=32) (actual time=0.007..1.044 rows=9,970 loops=1)

8. 6.216 9.970 ↑ 491.0 2 9,970

Materialize (cost=359.35..6,544.74 rows=982 width=201) (actual time=0.000..0.001 rows=2 loops=9,970)

9. 1.066 3.754 ↑ 491.0 2 1

Merge Join (cost=359.35..6,539.83 rows=982 width=201) (actual time=1.045..3.754 rows=2 loops=1)

  • Merge Cond: (ownorg.ownerorgid = o.id)
10. 1.651 1.651 ↑ 28.0 9,973 1

Index Scan using ix_ownergisareas_ownerorgid on ownergisareas ownorg (cost=0.42..18,567.84 rows=279,742 width=140) (actual time=0.008..1.651 rows=9,973 loops=1)

11. 0.025 1.037 ↑ 1.1 64 1

Sort (cost=358.78..358.96 rows=71 width=65) (actual time=1.034..1.037 rows=64 loops=1)

  • Sort Key: o.id
  • Sort Method: quicksort Memory: 43kB
12. 0.009 1.012 ↑ 1.0 70 1

Nested Loop (cost=0.00..356.59 rows=71 width=65) (actual time=0.064..1.012 rows=70 loops=1)

13. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on drawareatable (cost=0.00..1.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

14. 1.001 1.001 ↑ 1.0 70 1

Seq Scan on organisation o (cost=0.00..354.88 rows=71 width=33) (actual time=0.061..1.001 rows=70 loops=1)

  • Filter: (isprojectmodule AND (id <> 3883))
  • Rows Removed by Filter: 6400
15. 119.640 119.640 ↓ 0.0 0 19,940

Function Scan on y (cost=0.25..0.26 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=19,940)

  • Filter: y
  • Rows Removed by Filter: 1
16.          

SubPlan (forNested Loop)

17. 0.072 0.072 ↑ 1.0 1 4

Function Scan on st_intersects (cost=0.25..0.26 rows=1 width=1) (actual time=0.017..0.018 rows=1 loops=4)

18. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=16.90..16.90 rows=345 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on drawowngisareaintersect di (cost=0.00..16.90 rows=345 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: intersectedorg
Planning time : 0.559 ms