explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wDu8 : zielversion

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.118 50,921.393 ↓ 32.0 32 1

Nested Loop Left Join (cost=116.83..1,729.76 rows=1 width=119) (actual time=50,601.513..50,921.393 rows=32 loops=1)

2. 16.012 50,921.115 ↓ 32.0 32 1

Hash Semi Join (cost=116.70..1,729.61 rows=1 width=95) (actual time=50,601.499..50,921.115 rows=32 loops=1)

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

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

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

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

5. 263.707 263.940 ↓ 1.4 55,425 1

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

6. 0.233 0.233 ↑ 1.0 397 1

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

7.          

SubPlan (for Subquery Scan)

8. 0.183 0.183 ↑ 1.0 61 1

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

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 363
9. 0.203 50,593.956 ↓ 61.0 61 1

Hash (cost=107.30..107.30 rows=1 width=75) (actual time=50,593.956..50,593.956 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 50,535.549 50,593.753 ↓ 61.0 61 1

Nested Loop (cost=19.80..107.30 rows=1 width=75) (actual time=565.783..50,593.753 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. 0.639 0.639 ↑ 1.0 397 1

Seq Scan on factsets (cost=0.00..15.97 rows=397 width=109) (actual time=0.003..0.639 rows=397 loops=1)

12. 27.027 57.565 ↓ 64.5 258 397

Materialize (cost=19.80..19.88 rows=4 width=172) (actual time=0.076..0.145 rows=258 loops=397)

13. 0.114 30.538 ↓ 64.5 258 1

Subquery Scan on distinct_paths (cost=19.80..19.86 rows=4 width=172) (actual time=30.093..30.538 rows=258 loops=1)

14. 0.272 30.424 ↓ 64.5 258 1

Unique (cost=19.80..19.82 rows=4 width=258) (actual time=30.091..30.424 rows=258 loops=1)

15. 3.723 30.152 ↓ 64.5 258 1

Sort (cost=19.80..19.81 rows=4 width=258) (actual time=30.089..30.152 rows=258 loops=1)

  • Sort Key: fact_paths.path
  • Sort Method: quicksort Memory: 93kB
16. 9.276 26.429 ↓ 64.5 258 1

Bitmap Heap Scan on fact_paths (cost=4.32..19.76 rows=4 width=258) (actual time=17.218..26.429 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. 17.153 17.153 ↓ 68.2 273 1

Bitmap Index Scan on fact_paths_path_trgm (cost=0.00..4.32 rows=4 width=0) (actual time=17.153..17.153 rows=273 loops=1)

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

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

  • Index Cond: (id = fs.environment_id)