Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6
What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side of the Nested Loop which already has 3,444 rows. That's a hella-lot when you're only expecting one (which isn't even much of a "loop"). Anywayy..

Further examination as we move down shows that the Hash Join is really borked by the estimations arising from this,

Filter: (((properties."COMPOSITION")::text ~~ 'Mo%'::text) AND (((properties."NAME")::text ~~ '%VASP-ase-preopt%'::text) OR (properties."CALCULATOR_ID" = ANY ('{7,22,25}'::integer[]))))


PostgreSQL expects that to return one row. But it doesn't. And, that's really your problem. So some options here, that don't involve taking out a sledge hammer and disabling nested_loop


You can add an index or two to properties to help it potentially skip the seq scan entirely, or better estimate the return.

CREATE INDEX ON properties USING ( "TYPE_ID", "CALCULATOR_ID" );
-- the gist_trgm_ops may or may not be needed depending on selectivity of above.
CREATE INDEX ON properties USING GIST (
  "COMPOSITION" gist_trgm_ops,
  "NAME"        gist_trgm_ops
);
ANALYZE properties;

Alternatively, you can move the properties stuff to a CTE or subselect with OFFSET 0 which creates a fence.

WITH t AS (
  SELECT *
  FROM properties.
  WHERE "COMPOSITION" LIKE 'Mo%'
  AND (
    "NAME" LIKE '%VASP-ase-preopt%'
    OR "CALCULATOR_ID" IN (7,22,25)
  )
  AND "TYPE_ID" IN (6)
)
SELECT * FROM structures
JOIN t ON (
  structures.id = entries_properties.entry_id
)