explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pGTs : Optimization for: my; plan #FjeV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 33.598 519.115 ↓ 6.5 1,146 1

Nested Loop Left Join (cost=35,104.48..44,756.54 rows=177 width=474) (actual time=465.831..519.115 rows=1,146 loops=1)

2. 1.103 478.641 ↓ 6.5 1,146 1

Nested Loop Left Join (cost=35,103.63..43,010.73 rows=177 width=487) (actual time=465.437..478.641 rows=1,146 loops=1)

3. 1.281 475.246 ↓ 6.5 1,146 1

Nested Loop Left Join (cost=35,103.21..42,929.72 rows=177 width=448) (actual time=465.430..475.246 rows=1,146 loops=1)

4. 4.851 469.381 ↓ 6.5 1,146 1

Merge Left Join (cost=35,102.78..41,597.69 rows=177 width=390) (actual time=465.412..469.381 rows=1,146 loops=1)

  • Merge Cond: (o.objekti_id = objekti_etaze.objekti_id)
5. 1.566 424.946 ↓ 6.5 1,146 1

Sort (cost=35,102.36..35,102.80 rows=177 width=374) (actual time=424.550..424.946 rows=1,146 loops=1)

  • Sort Key: o.objekti_id
  • Sort Method: quicksort Memory: 677kB
6. 0.353 423.380 ↓ 6.5 1,146 1

Hash Left Join (cost=26,937.54..35,095.75 rows=177 width=374) (actual time=379.193..423.380 rows=1,146 loops=1)

  • Hash Cond: (o.projekti_id = p.projekti_id)
7. 10.395 422.999 ↓ 6.5 1,146 1

Hash Left Join (cost=26,936.47..35,092.24 rows=177 width=366) (actual time=379.151..422.999 rows=1,146 loops=1)

  • Hash Cond: (o.objekti_id = d.objekti_id)
8. 0.555 33.574 ↓ 6.5 1,146 1

Hash Join (cost=4.92..8,155.82 rows=177 width=318) (actual time=0.109..33.574 rows=1,146 loops=1)

  • Hash Cond: (o.fishnet_id = f.fishnet_id)
9. 32.983 32.983 ↑ 1.3 3,956 1

Seq Scan on objekti o (cost=0.00..8,130.38 rows=5,000 width=318) (actual time=0.021..32.983 rows=3,956 loops=1)

  • Filter: ((status > 0) AND (projekti_id = 10))
  • Rows Removed by Filter: 71694
10. 0.008 0.036 ↓ 1.6 13 1

Hash (cost=4.83..4.83 rows=8 width=12) (actual time=0.036..0.036 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.028 0.028 ↓ 1.6 13 1

Seq Scan on fishnet f (cost=0.00..4.83 rows=8 width=12) (actual time=0.009..0.028 rows=13 loops=1)

  • Filter: (fishnet_sifra = 3)
  • Rows Removed by Filter: 188
12. 17.833 379.030 ↓ 378.2 75,649 1

Hash (cost=26,929.04..26,929.04 rows=200 width=52) (actual time=379.030..379.030 rows=75,649 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
13. 7.031 361.197 ↓ 378.2 75,649 1

Subquery Scan on d (cost=26,924.54..26,929.04 rows=200 width=52) (actual time=321.775..361.197 rows=75,649 loops=1)

14. 102.599 354.166 ↓ 378.2 75,649 1

HashAggregate (cost=26,924.54..26,927.04 rows=200 width=52) (actual time=321.774..354.166 rows=75,649 loops=1)

  • Group Key: kk.objekti_id
15. 53.412 251.567 ↓ 1.0 104,876 1

GroupAggregate (cost=16,726.58..24,828.72 rows=104,791 width=40) (actual time=88.460..251.567 rows=104,876 loops=1)

  • Group Key: kk.katovi_id
16. 42.295 198.155 ↓ 1.0 131,722 1

Merge Left Join (cost=16,726.58..22,818.89 rows=128,256 width=28) (actual time=88.444..198.155 rows=131,722 loops=1)

  • Merge Cond: (kk.katovi_id = ddd.katovi_id)
17. 32.277 32.277 ↓ 1.0 104,876 1

Index Scan using katovi_pkey on katovi kk (cost=0.29..3,592.75 rows=104,791 width=12) (actual time=0.006..32.277 rows=104,876 loops=1)

18. 11.326 123.583 ↓ 1.0 131,721 1

Materialize (cost=16,726.23..17,367.51 rows=128,256 width=24) (actual time=88.433..123.583 rows=131,721 loops=1)

19. 91.885 112.257 ↓ 1.0 131,721 1

Sort (cost=16,726.23..17,046.87 rows=128,256 width=24) (actual time=88.429..112.257 rows=131,721 loops=1)

  • Sort Key: ddd.katovi_id
  • Sort Method: external merge Disk: 4368kB
20. 20.372 20.372 ↓ 1.0 131,721 1

Seq Scan on dijelovi ddd (cost=0.00..3,212.56 rows=128,256 width=24) (actual time=0.011..20.372 rows=131,721 loops=1)

21. 0.005 0.028 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=16) (actual time=0.028..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on projekti p (cost=0.00..1.06 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=1)

  • Filter: (projekti_id = 10)
  • Rows Removed by Filter: 14
23. 6.544 39.584 ↑ 1.2 53,637 1

Materialize (cost=0.42..6,330.12 rows=65,091 width=24) (actual time=0.030..39.584 rows=53,637 loops=1)

24. 20.781 33.040 ↑ 1.2 53,637 1

GroupAggregate (cost=0.42..5,516.48 rows=65,091 width=24) (actual time=0.027..33.040 rows=53,637 loops=1)

  • Group Key: objekti_etaze.objekti_id
25. 12.259 12.259 ↑ 1.3 67,429 1

Index Only Scan using objekti_etaze_pkey on objekti_etaze (cost=0.42..4,221.88 rows=85,825 width=16) (actual time=0.020..12.259 rows=67,429 loops=1)

  • Heap Fetches: 25025
26. 4.584 4.584 ↑ 1.0 1 1,146

Index Scan using objekti_images_index on objekti_images oi (cost=0.43..7.52 rows=1 width=70) (actual time=0.004..0.004 rows=1 loops=1,146)

  • Index Cond: ((objekti_id = o.objekti_id) AND (images_id = o.foto_1))
27. 2.292 2.292 ↑ 1.0 1 1,146

Index Scan using images_pkey on images i (cost=0.42..0.45 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=1,146)

  • Index Cond: (images_id = oi.images_id)
28. 1.146 6.876 ↑ 1.0 1 1,146

Nested Loop Left Join (cost=0.85..7.97 rows=1 width=113) (actual time=0.005..0.006 rows=1 loops=1,146)

29. 3.438 3.438 ↑ 1.0 1 1,146

Index Scan using objekti_images_index on objekti_images oi_1 (cost=0.43..7.52 rows=1 width=70) (actual time=0.003..0.003 rows=1 loops=1,146)

  • Index Cond: ((objekti_id = o.objekti_id) AND (images_id = o.foto_2))
30. 2.292 2.292 ↑ 1.0 1 1,146

Index Scan using images_pkey on images i_1 (cost=0.42..0.45 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=1,146)

  • Index Cond: (images_id = oi_1.images_id)
Planning time : 2.918 ms
Execution time : 528.256 ms