explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 59BR : with Nested Loop

Settings
# exclusive inclusive rows x rows loops node
1. 3,121.007 37,766.486 ↓ 191.0 191 1

Nested Loop (cost=9,920.53..15,803.67 rows=1 width=600) (actual time=253.638..37,766.486 rows=191 loops=1)

  • Output: properties.id, properties."NAME", properties."TYPE_ID", properties."_VALUE", properties."CALCULATOR_ID", properties."COMPOSITION", properties."_EXTRA_SETTINGS", properties."DATETIME", properties."CONTRIBUTOR", properties.ouid, properties.ur, properties.uw, properties.ogid, properties.gr, properties.gw, properties.ar, properties.aw
  • Join Filter: (entries_properties.entry_id = structures.id)
  • Rows Removed by Join Filter: 4607881
  • Buffers: shared hit=7227772
2. 20.159 257.541 ↓ 1,338.0 1,338 1

Hash Join (cost=9,912.78..10,484.45 rows=1 width=604) (actual time=224.586..257.541 rows=1,338 loops=1)

  • Output: properties.id, properties."NAME", properties."TYPE_ID", properties."_VALUE", properties."CALCULATOR_ID", properties."COMPOSITION", properties."_EXTRA_SETTINGS", properties."DATETIME", properties."CONTRIBUTOR", properties.ouid, properties.ur, properties.uw, properties.ogid, properties.gr, properties.gw, properties.ar, properties.aw, entries_properties.entry_id
  • Hash Cond: (entries_properties.property_id = properties.id)
  • Buffers: shared hit=22639
3. 17.131 17.131 ↑ 1.0 27,248 1

Seq Scan on public.entries_properties (cost=0.00..469.48 rows=27,248 width=8) (actual time=0.011..17.131 rows=27,248 loops=1)

  • Output: entries_properties.entry_id, entries_properties.property_id, entries_properties."NAME
  • Buffers: shared hit=197
4. 1.210 220.251 ↓ 1,339.0 1,339 1

Hash (cost=9,912.76..9,912.76 rows=1 width=600) (actual time=220.251..220.251 rows=1,339 loops=1)

  • Output: properties.id, properties."NAME", properties."TYPE_ID", properties."_VALUE", properties."CALCULATOR_ID", properties."COMPOSITION", properties."_EXTRA_SETTINGS", properties."DATETIME", properties."CONTRIBUTOR", properties.ouid, properties.ur, properties.uw, properties.ogid, properties.gr, properties.gw, properties.ar, properties.aw
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 708kB
  • Buffers: shared hit=22442
5. 20.456 219.041 ↓ 1,339.0 1,339 1

Subquery Scan on properties (cost=7.75..9,912.76 rows=1 width=600) (actual time=16.803..219.041 rows=1,339 loops=1)

  • Output: properties.id, properties."NAME", properties."TYPE_ID", properties."_VALUE", properties."CALCULATOR_ID", properties."COMPOSITION", properties."_EXTRA_SETTINGS", properties."DATETIME", properties."CONTRIBUTOR", properties.ouid, properties.ur, properties.uw, properties.ogid, properties.gr, properties.gw, properties.ar, properties.aw
  • Filter: (((properties."COMPOSITION")::text ~~ 'Mo%'::text) AND (((properties."NAME")::text ~~ '%VASP-ase-preopt%'::text) OR (properties."CALCULATOR_ID" = ANY ('{7,22,25}'::integer[]))))
  • Rows Removed by Filter: 17734
  • Buffers: shared hit=22442
6. 198.483 198.585 ↑ 1.0 19,073 1

Seq Scan on public.properties properties_1 (cost=7.75..9,555.15 rows=19,073 width=600) (actual time=0.182..198.585 rows=19,073 loops=1)

  • Output: properties_1.id, properties_1."NAME", properties_1."TYPE_ID", properties_1."_VALUE", properties_1."CALCULATOR_ID", properties_1."COMPOSITION", properties_1."_EXTRA_SETTINGS", properties_1."DATETIME", properties_1."CONTRIBUTOR", properties_1.ouid, properties_1.ur, properties_1.uw, properties_1.ogid, properties_1.gr, properties_1.gw, properties_1.ar, properties_1.aw
  • Filter: ((properties_1."TYPE_ID" = 6) AND (((properties_1.ouid = get_current_user_id()) AND properties_1.ur) OR ((hashed SubPlan 1) AND properties_1.gr) OR properties_1.ar))
  • Rows Removed by Filter: 4035
  • Buffers: shared hit=22442
7.          

SubPlan (for Seq Scan)

8. 0.102 0.102 ↑ 1,000.0 1 1

Result (cost=0.00..5.25 rows=1,000 width=4) (actual time=0.100..0.102 rows=1 loops=1)

  • Output: get_current_groups_id()
  • Buffers: shared hit=3
9. 34,387.880 34,387.938 ↑ 3.4 3,444 1,338

Seq Scan on public.structures (cost=7.75..5,057.91 rows=11,614 width=5,060) (actual time=0.008..25.701 rows=3,444 loops=1,338)

  • Output: structures.id, NULL::character varying(32), NULL::character varying(128), NULL::character varying(128), NULL::character varying(1024), NULL::character varying(1024), NULL::character varying(1024), NULL::character varying(1024), NULL::integer, NULL::integer, NULL::character varying(1024), NULL::character varying(1024), NULL::character varying(1024), NULL::character varying(1024), NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::timestamp without time zone, NULL::integer, NULL::boolean, NULL::boolean, NULL::integer, NULL::boolean, NULL::boolean, NULL::boolean, NULL::boolean
  • Filter: (((structures."STRUKTURBERICHT")::text <> ''::text) AND (((structures.ouid = get_current_user_id()) AND structures.ur) OR ((hashed SubPlan 2) AND structures.gr) OR structures.ar))
  • Rows Removed by Filter: 8179
  • Buffers: shared hit=7205133
10.          

SubPlan (for Seq Scan)

11. 0.058 0.058 ↑ 1,000.0 1 1

Result (cost=0.00..5.25 rows=1,000 width=4) (actual time=0.057..0.058 rows=1 loops=1)

  • Output: get_current_groups_id()
  • Buffers: shared hit=3
Planning time : 0.839 ms
Execution time : 37,766.724 ms