explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rKjZ : zielversion2

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 47,927.891 ↓ 32.0 32 1

Nested Loop Left Join (cost=16,236.96..17,849.89 rows=1 width=96) (actual time=47,638.640..47,927.891 rows=32 loops=1)

2. 14.299 47,927.683 ↓ 32.0 32 1

Hash Semi Join (cost=16,236.83..17,849.74 rows=1 width=95) (actual time=47,638.631..47,927.683 rows=32 loops=1)

  • Hash Cond: ((fs.key = (distinct_paths.name)::text) AND (fs.certname = factsets.certname))
3. 23.193 281.824 ↓ 2.5 50,540 1

Subquery Scan on fs (cost=9.39..1,518.08 rows=19,850 width=95) (actual time=0.917..281.824 rows=50,540 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 4885
4. 20.113 258.480 ↓ 1.4 55,425 1

Result (cost=0.00..1,012.44 rows=39,700 width=95) (actual time=0.724..258.480 rows=55,425 loops=1)

5. 238.190 238.367 ↓ 1.4 55,425 1

ProjectSet (cost=0.00..218.44 rows=39,700 width=63) (actual time=0.719..238.367 rows=55,425 loops=1)

6. 0.177 0.177 ↑ 1.0 397 1

Seq Scan on factsets factsets_1 (cost=0.00..15.97 rows=397 width=109) (actual time=0.007..0.177 rows=397 loops=1)

7.          

SubPlan (for Subquery Scan)

8. 0.151 0.151 ↑ 1.0 61 1

Seq Scan on certnames (cost=0.00..9.24 rows=61 width=23) (actual time=0.013..0.151 rows=61 loops=1)

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 363
9. 0.201 47,631.560 ↓ 7.6 61 1

Hash (cost=16,227.32..16,227.32 rows=8 width=75) (actual time=47,631.560..47,631.560 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 47,573.834 47,631.359 ↓ 7.6 61 1

Nested Loop (cost=1,525.91..16,227.32 rows=8 width=75) (actual time=1,620.573..47,631.359 rows=61 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC distinct_paths.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC distinct_paths.path_array) = '"webdms"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC distinct_paths.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC distinct_paths.path_array)) = 'string'::text))
  • Rows Removed by Join Filter: 102365
11. 1.548 1.548 ↑ 1.0 397 1

Index Scan using factsets_certname_idx on factsets (cost=0.27..73.80 rows=397 width=109) (actual time=0.012..1.548 rows=397 loops=1)

12. 24.117 55.977 ↑ 3.2 258 397

Materialize (cost=1,525.64..1,542.00 rows=818 width=172) (actual time=0.079..0.141 rows=258 loops=397)

13. 0.197 31.860 ↑ 3.2 258 1

Subquery Scan on distinct_paths (cost=1,525.64..1,537.91 rows=818 width=172) (actual time=31.150..31.860 rows=258 loops=1)

14. 0.398 31.663 ↑ 3.2 258 1

Unique (cost=1,525.64..1,529.73 rows=818 width=258) (actual time=31.148..31.663 rows=258 loops=1)

15. 2.924 31.265 ↑ 3.2 258 1

Sort (cost=1,525.64..1,527.68 rows=818 width=258) (actual time=31.146..31.265 rows=258 loops=1)

  • Sort Key: fact_paths.path
  • Sort Method: quicksort Memory: 93kB
16. 8.070 28.341 ↑ 3.2 258 1

Bitmap Heap Scan on fact_paths (cost=148.66..1,486.06 rows=818 width=258) (actual time=20.338..28.341 rows=258 loops=1)

  • Recheck Cond: ((path ~ '^lieferkomponenteninstanzen#~(?:((?!\#\~).)+|.?)#~anwendung$'::text) AND (path IS NOT NULL))
  • Rows Removed by Index Recheck: 15
  • Heap Blocks: exact=131
17. 20.271 20.271 ↑ 3.0 273 1

Bitmap Index Scan on fact_paths_path_trgm (cost=0.00..148.46 rows=818 width=0) (actual time=20.271..20.271 rows=273 loops=1)

  • Index Cond: ((path ~ '^lieferkomponenteninstanzen#~(?:((?!\#\~).)+|.?)#~anwendung$'::text) AND (path IS NOT NULL))
18. 0.128 0.128 ↑ 1.0 1 32

Index Scan using environments_pkey on environments env (cost=0.13..0.15 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=32)

  • Index Cond: (id = fs.environment_id)