explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6JDO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 145.651 979.777 ↓ 3.2 15,932 1

Gather (cost=15,439.57..113,510.82 rows=4,931 width=4) (actual time=215.465..979.777 rows=15,932 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 7.975 834.126 ↓ 2.6 5,311 3 / 3

Nested Loop Left Join (cost=14,439.57..112,017.72 rows=2,055 width=4) (actual time=131.027..834.126 rows=5,311 loops=3)

3. 8.608 597.792 ↓ 2.6 5,311 3 / 3

Nested Loop Left Join (cost=14,439.13..88,040.16 rows=2,055 width=4) (actual time=130.106..597.792 rows=5,311 loops=3)

4. 7.288 530.767 ↓ 2.6 5,311 3 / 3

Nested Loop (cost=14,438.71..86,684.39 rows=2,055 width=8) (actual time=129.592..530.767 rows=5,311 loops=3)

5. 3.362 374.780 ↓ 2.6 5,311 3 / 3

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

6. 3.321 339.554 ↓ 2.6 5,311 3 / 3

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

7. 12.445 304.287 ↓ 2.6 5,324 3 / 3

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

8. 5.673 259.888 ↓ 2.5 5,326 3 / 3

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

  • Hash Cond: (plot.location_type_id = cache_ttplottype.id)
9. 18.046 246.015 ↓ 2.1 5,370 3 / 3

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

  • Hash Cond: (csf.location_id = plot.id)
10. 123.451 126.109 ↑ 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.355..126.109 rows=5,795 loops=3)

  • Recheck Cond: (website_id = 32)
  • Filter: (survey_id <> 281)
  • Rows Removed by Filter: 342
  • Heap Blocks: exact=5,396
11. 2.658 2.658 ↓ 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.974..7.974 rows=18,534 loops=1)

  • Index Cond: (website_id = 32)
12. 25.691 101.860 ↑ 1.3 67,534 3 / 3

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,072kB
13. 76.169 76.169 ↑ 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=1.964..76.169 rows=67,534 loops=3)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3,416
14. 2.435 8.200 ↑ 1.0 14,347 3 / 3

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 633kB
15. 5.765 5.765 ↑ 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=1.699..5.765 rows=14,347 loops=3)

16. 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
17. 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
18. 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)
19. 148.699 148.699 ↑ 1.0 1 15,932 / 3

Index Only Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional csnf (cost=0.43..4.47 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=15,932)

  • Index Cond: (id = csf.id)
  • Heap Fetches: 9,502
20. 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
21. 228.359 228.359 ↓ 0.0 0 15,932 / 3

Index Scan using fki_sample_attribute_values_sample on sample_attribute_values savlinktosurveyone (cost=0.44..11.66 rows=1 width=4) (actual time=0.040..0.043 rows=0 loops=15,932)

  • Index Cond: (sample_id = csf.id)
  • Filter: ((NOT deleted) AND (int_value <> 0) AND (sample_attribute_id = 227))
  • Rows Removed by Filter: 10