explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z1A2

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 2,030.041 ↓ 0.0 0 1

Nested Loop Anti Join (cost=10.41..1,866,974.49 rows=1 width=128) (actual time=2,030.041..2,030.041 rows=0 loops=1)

  • Join Filter: ((((c.relname)::information_schema.sql_identifier)::text = (c_1.relname)::text) AND (((nc.nspname)::information_schema.sql_identifier)::text = (n.nspname)::text))
  • Rows Removed by Join Filter: 90
2. 0.019 1,053.825 ↑ 1.0 1 1

Nested Loop Anti Join (cost=5.54..940,220.82 rows=1 width=128) (actual time=1,035.435..1,053.825 rows=1 loops=1)

  • Join Filter: ((((c.relname)::information_schema.sql_identifier)::text = (c_2.relname)::text) AND (((nc.nspname)::information_schema.sql_identifier)::text = (n_1.nspname)::text))
  • Rows Removed by Join Filter: 30
3. 0.003 18.908 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.68..22,738.19 rows=1 width=128) (actual time=0.518..18.908 rows=1 loops=1)

4. 0.018 18.901 ↑ 1.0 1 1

Nested Loop (cost=0.00..22,732.45 rows=1 width=132) (actual time=0.513..18.901 rows=1 loops=1)

  • Join Filter: ((nc.oid = c.relnamespace) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'spatial_ref_sys'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'geometry_columns'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'geography_columns'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'raster_columns'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'raster_overviews'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'non_postgis_tables'::text)) AND ((((nc.nspname)::information_schema.sql_identifier)::text <> 'public'::text) OR (((c.relname)::information_schema.sql_identifier)::text <> 'non_postgis_matviews'::text)))
  • Rows Removed by Join Filter: 1
5. 18.153 18.153 ↑ 4.5 2 1

Seq Scan on pg_class c (cost=0.00..22,703.26 rows=9 width=72) (actual time=0.448..18.153 rows=2 loops=1)

  • Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND ((((relname)::information_schema.sql_identifier)::character varying(256))::text = '_01_02_lokalplan'::text) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
  • Rows Removed by Filter: 9728
6. 0.008 0.730 ↑ 2.0 1 2

Materialize (cost=0.00..27.67 rows=2 width=68) (actual time=0.026..0.365 rows=1 loops=2)

7. 0.722 0.722 ↑ 2.0 1 1

Seq Scan on pg_namespace nc (cost=0.00..27.66 rows=2 width=68) (actual time=0.046..0.722 rows=1 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text <> 'settings'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'pg_catalog'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'information_schema'::text) AND ((((nspname)::information_schema.sql_identifier)::character varying(256))::text = 'konflikt_bke'::text))
  • Rows Removed by Filter: 475
8. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.68..5.73 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

9. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pg_type_oid_index on pg_type t (cost=0.41..4.42 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (c.reloftype = oid)
10. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.27..1.29 rows=1 width=4) (never executed)

  • Index Cond: (oid = t.typnamespace)
  • Heap Fetches: 0
11. 0.015 1,034.898 ↓ 2.1 30 1

Materialize (cost=4.86..917,482.25 rows=14 width=128) (actual time=1,033.043..1,034.898 rows=30 loops=1)

12. 1.388 1,034.883 ↓ 2.1 30 1

Nested Loop (cost=4.86..917,482.18 rows=14 width=128) (actual time=1,033.038..1,034.883 rows=30 loops=1)

  • Join Filter: (c_2.relnamespace = n_1.oid)
  • Rows Removed by Join Filter: 14250
13. 0.099 0.099 ↑ 1.0 476 1

Seq Scan on pg_namespace n_1 (cost=0.00..15.76 rows=476 width=68) (actual time=0.001..0.099 rows=476 loops=1)

14. 0.449 1,033.396 ↓ 2.1 30 476

Materialize (cost=4.86..917,366.50 rows=14 width=68) (actual time=0.003..2.171 rows=30 loops=476)

15. 0.014 1,032.947 ↓ 2.1 30 1

Nested Loop (cost=4.86..917,366.43 rows=14 width=68) (actual time=1.214..1,032.947 rows=30 loops=1)

16. 5.384 1,032.613 ↑ 102.0 32 1

Hash Join (cost=4.45..908,547.90 rows=3,265 width=4) (actual time=0.405..1,032.613 rows=32 loops=1)

  • Hash Cond: (a_1.atttypid = t_2.oid)
17. 1,027.216 1,027.216 ↑ 281.6 94,589 1

Seq Scan on pg_attribute a_1 (cost=0.00..808,609.48 rows=26,640,350 width=8) (actual time=0.002..1,027.216 rows=94,589 loops=1)

  • Filter: (NOT attisdropped)
  • Rows Removed by Filter: 439
18. 0.004 0.013 ↑ 1.0 1 1

Hash (cost=4.44..4.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pg_type_typname_nsp_index on pg_type t_2 (cost=0.41..4.44 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (typname = 'raster'::name)
20. 0.320 0.320 ↑ 1.0 1 32

Index Scan using pg_class_oid_index on pg_class c_2 (cost=0.41..2.69 rows=1 width=72) (actual time=0.010..0.010 rows=1 loops=32)

  • Index Cond: (oid = a_1.attrelid)
  • Filter: ((NOT pg_is_other_temp_schema(relnamespace)) AND has_table_privilege(oid, 'SELECT'::text) AND ((relkind)::text = ANY ('{r,v,m,f}'::text[])))
  • Rows Removed by Filter: 0
21. 0.039 976.188 ↑ 1.9 91 1

Materialize (cost=4.86..926,748.85 rows=175 width=128) (actual time=974.136..976.188 rows=91 loops=1)

22. 0.110 976.149 ↑ 1.9 91 1

Nested Loop Left Join (cost=4.86..926,747.97 rows=175 width=128) (actual time=974.121..976.149 rows=91 loops=1)

  • Join Filter: ((s_2.connamespace = n.oid) AND (s_2.conrelid = c_1.oid) AND (a.attnum = ANY (s_2.conkey)))
  • Rows Removed by Join Filter: 2002
23. 0.019 974.310 ↑ 1.9 91 1

Nested Loop Left Join (cost=4.86..923,969.15 rows=175 width=138) (actual time=972.491..974.310 rows=91 loops=1)

  • Join Filter: ((s_1.connamespace = n.oid) AND (s_1.conrelid = c_1.oid) AND (a.attnum = ANY (s_1.conkey)))
24. 0.013 972.198 ↑ 1.9 91 1

Nested Loop Left Join (cost=4.86..921,300.64 rows=175 width=138) (actual time=970.395..972.198 rows=91 loops=1)

  • Join Filter: ((s.connamespace = n.oid) AND (s.conrelid = c_1.oid) AND (a.attnum = ANY (s.conkey)))
25. 1.383 969.000 ↑ 1.9 91 1

Nested Loop (cost=4.86..918,632.12 rows=175 width=138) (actual time=967.210..969.000 rows=91 loops=1)

  • Join Filter: (c_1.relnamespace = n.oid)
  • Rows Removed by Join Filter: 19117
26. 0.007 0.007 ↑ 47.6 10 1

Seq Scan on pg_namespace n (cost=0.00..15.76 rows=476 width=68) (actual time=0.004..0.007 rows=10 loops=1)

27. 1.182 967.610 ↓ 11.0 1,921 10

Materialize (cost=4.86..917,367.30 rows=175 width=74) (actual time=0.066..96.761 rows=1,921 loops=10)

28. 1.190 966.428 ↓ 11.9 2,081 1

Nested Loop (cost=4.86..917,366.43 rows=175 width=74) (actual time=0.661..966.428 rows=2,081 loops=1)

29. 5.435 954.808 ↑ 1.6 2,086 1

Hash Join (cost=4.45..908,547.90 rows=3,265 width=6) (actual time=0.411..954.808 rows=2,086 loops=1)

  • Hash Cond: (a.atttypid = t_1.oid)
30. 949.352 949.352 ↑ 281.6 94,589 1

Seq Scan on pg_attribute a (cost=0.00..808,609.48 rows=26,640,350 width=10) (actual time=0.004..949.352 rows=94,589 loops=1)

  • Filter: (NOT attisdropped)
  • Rows Removed by Filter: 439
31. 0.004 0.021 ↑ 1.0 1 1

Hash (cost=4.44..4.44 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.017 0.017 ↑ 1.0 1 1

Index Scan using pg_type_typname_nsp_index on pg_type t_1 (cost=0.41..4.44 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (typname = 'geometry'::name)
33. 10.430 10.430 ↑ 1.0 1 2,086

Index Scan using pg_class_oid_index on pg_class c_1 (cost=0.41..2.69 rows=1 width=72) (actual time=0.005..0.005 rows=1 loops=2,086)

  • Index Cond: (oid = a.attrelid)
  • Filter: ((relname <> 'raster_columns'::name) AND (NOT pg_is_other_temp_schema(relnamespace)) AND has_table_privilege(oid, 'SELECT'::text) AND (relkind = ANY ('{r,v,m,f}'::"char"[])))
  • Rows Removed by Filter: 0
34. 0.004 3.185 ↓ 0.0 0 91

Materialize (cost=0.00..2,663.27 rows=1 width=31) (actual time=0.035..0.035 rows=0 loops=91)

35. 3.181 3.181 ↓ 0.0 0 1

Seq Scan on pg_constraint s (cost=0.00..2,663.26 rows=1 width=31) (actual time=3.181..3.181 rows=0 loops=1)

  • Filter: (consrc ~~* '%geometrytype(% = %'::text)
  • Rows Removed by Filter: 1675
36. 0.000 2.093 ↓ 0.0 0 91

Materialize (cost=0.00..2,663.27 rows=1 width=31) (actual time=0.023..0.023 rows=0 loops=91)

37. 2.093 2.093 ↓ 0.0 0 1

Seq Scan on pg_constraint s_1 (cost=0.00..2,663.26 rows=1 width=31) (actual time=2.093..2.093 rows=0 loops=1)

  • Filter: (consrc ~~* '%ndims(% = %'::text)
  • Rows Removed by Filter: 1675
38. 0.108 1.729 ↑ 1.0 22 91

Materialize (cost=0.00..2,663.37 rows=22 width=31) (actual time=0.001..0.019 rows=22 loops=91)

39. 1.621 1.621 ↑ 1.0 22 1

Seq Scan on pg_constraint s_2 (cost=0.00..2,663.26 rows=22 width=31) (actual time=0.054..1.621 rows=22 loops=1)

  • Filter: (consrc ~~* '%srid(% = %'::text)
  • Rows Removed by Filter: 1653
Planning time : 3.576 ms
Execution time : 2,030.485 ms