explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6OXd : Optimization for: plan #6JDO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 81.457 750.102 ↓ 3.2 15,932 1

Gather (cost=15,439.13..96,137.85 rows=4,931 width=36) (actual time=174.420..750.102 rows=15,932 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 28.208 668.645 ↓ 2.6 5,311 3 / 3

Nested Loop Left Join (cost=14,439.13..94,644.75 rows=2,055 width=36) (actual time=119.409..668.645 rows=5,311 loops=3)

3. 8.234 582.020 ↓ 2.6 5,311 3 / 3

Nested Loop (cost=14,438.71..93,283.84 rows=2,055 width=86) (actual time=118.868..582.020 rows=5,311 loops=3)

4. 3.401 382.602 ↓ 2.6 5,311 3 / 3

Nested Loop (cost=14,438.28..77,505.61 rows=2,055 width=8) (actual time=118.187..382.602 rows=5,311 loops=3)

5. 6.088 347.337 ↓ 2.6 5,311 3 / 3

Nested Loop (cost=14,437.86..76,458.33 rows=2,060 width=12) (actual time=117.639..347.337 rows=5,311 loops=3)

6. 10.037 309.303 ↓ 2.6 5,324 3 / 3

Nested Loop (cost=14,437.44..73,591.91 rows=2,062 width=12) (actual time=116.527..309.303 rows=5,324 loops=3)

7. 4.814 267.312 ↓ 2.5 5,326 3 / 3

Hash Join (cost=14,437.02..72,159.85 rows=2,166 width=16) (actual time=115.898..267.312 rows=5,326 loops=3)

  • Hash Cond: (plot.location_type_id = cache_ttplottype.id)
8. 13.197 255.919 ↓ 2.1 5,370 3 / 3

Parallel Hash Join (cost=13,772.75..71,488.76 rows=2,597 width=20) (actual time=109.096..255.919 rows=5,370 loops=3)

  • Hash Cond: (csf.location_id = plot.id)
9. 142.310 144.732 ↑ 1.3 5,795 3 / 3

Parallel Bitmap Heap Scan on cache_samples_functional csf (cost=690.98..58,387.45 rows=7,448 width=12) (actual time=10.163..144.732 rows=5,795 loops=3)

  • Recheck Cond: (website_id = 32)
  • Filter: (survey_id <> 281)
  • Rows Removed by Filter: 342
  • Heap Blocks: exact=5790
10. 2.422 2.422 ↓ 1.0 18,534 1 / 3

Bitmap Index Scan on ix_cache_samples_functional_website_id (cost=0.00..686.51 rows=17,877 width=0) (actual time=7.265..7.265 rows=18,534 loops=1)

  • Index Cond: (website_id = 32)
11. 24.117 97.990 ↑ 1.3 67,534 3 / 3

Parallel Hash (cost=11,993.59..11,993.59 rows=87,054 width=12) (actual time=97.990..97.990 rows=67,534 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 11072kB
12. 73.873 73.873 ↑ 1.3 67,534 3 / 3

Parallel Seq Scan on locations plot (cost=0.00..11,993.59 rows=87,054 width=12) (actual time=2.322..73.873 rows=67,534 loops=3)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3416
13. 2.032 6.579 ↑ 1.0 14,347 3 / 3

Hash (cost=484.12..484.12 rows=14,412 width=4) (actual time=6.579..6.579 rows=14,347 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 633kB
14. 4.547 4.547 ↑ 1.0 14,347 3 / 3

Seq Scan on cache_termlists_terms cache_ttplottype (cost=0.00..484.12 rows=14,412 width=4) (actual time=0.337..4.547 rows=14,347 loops=3)

15. 31.954 31.954 ↑ 1.0 1 15,977 / 3

Index Scan using pk_locations on locations square (cost=0.42..0.66 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=15,977)

  • Index Cond: (id = plot.parent_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
16. 31.946 31.946 ↑ 1.0 1 15,973 / 3

Index Scan using pk_users on users u (cost=0.42..1.39 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=15,973)

  • Index Cond: (id = csf.created_by_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
17. 31.864 31.864 ↑ 1.0 1 15,932 / 3

Index Scan using pk_people on people p (cost=0.42..0.51 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=15,932)

  • Index Cond: (id = u.person_id)
  • Filter: (NOT deleted)
18. 191.184 191.184 ↑ 1.0 1 15,932 / 3

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional csnf (cost=0.43..7.68 rows=1 width=82) (actual time=0.036..0.036 rows=1 loops=15,932)

  • Index Cond: (id = csf.id)
19. 58.417 58.417 ↑ 1.0 1 15,932 / 3

Index Scan using fki_location_attribute_values_location on location_attribute_values lavplotname (cost=0.42..0.65 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=15,932)

  • Index Cond: (location_id = plot.id)
  • Filter: ((NOT deleted) AND (location_attribute_id = 118))
  • Rows Removed by Filter: 6
Planning time : 5.724 ms