explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PG8E : no nested loop

Settings
# exclusive inclusive rows x rows loops node
1. 2.696 272.185 ↓ 191.0 191 1

Hash Join (cost=10,492.21..15,702.07 rows=1 width=600) (actual time=242.091..272.185 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
  • Hash Cond: (structures.id = entries_properties.entry_id)
  • Buffers: shared hit=28027
2. 27.470 27.555 ↑ 3.4 3,444 1

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

  • 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=5388
3.          

SubPlan (for Seq Scan)

4. 0.085 0.085 ↑ 1,000.0 1 1

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

  • Output: get_current_groups_id()
  • Buffers: shared hit=3
5. 1.311 241.934 ↓ 1,338.0 1,338 1

Hash (cost=10,484.45..10,484.45 rows=1 width=604) (actual time=241.934..241.934 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
  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 713kB
  • Buffers: shared hit=22639
6. 20.521 240.623 ↓ 1,338.0 1,338 1

Hash Join (cost=9,912.78..10,484.45 rows=1 width=604) (actual time=206.130..240.623 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
7. 18.569 18.569 ↑ 1.0 27,248 1

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

  • Output: entries_properties.entry_id, entries_properties.property_id, entries_properties."NAME
  • Buffers: shared hit=197
8. 1.202 201.533 ↓ 1,339.0 1,339 1

Hash (cost=9,912.76..9,912.76 rows=1 width=600) (actual time=201.533..201.533 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
9. 19.129 200.331 ↓ 1,339.0 1,339 1

Subquery Scan on properties (cost=7.75..9,912.76 rows=1 width=600) (actual time=13.868..200.331 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
10. 181.144 181.202 ↑ 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.092..181.202 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
11.          

SubPlan (for Seq Scan)

12. 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.669 ms
Execution time : 272.426 ms