11

I've a trouble with PostgreSQL 9.6 query planning. My query looks like this:

SET role plain_user;

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

I have Row-Level Security enabled for the above-used tables.

I did VACUUM ANALYZE before running the queries, but it didn't help.

I know that it is not a good practice to set enable_nestloop = False, and any other similar options for the planner. But how could I "convince" the planner to use hash joins without disabling nested loops?

Rewriting the query is an option.

If I run the same query under a role that bypasses RLS, then it is executed very fast. The row-level security policy looks like this:

CREATE POLICY properties_select
ON properties
FOR SELECT
USING (
  (
    properties.ouid = get_current_user_id()
    AND properties.ur
  )
  OR (
    properties.ogid in (select get_current_groups_id())
    AND properties.gr
  )
  OR properties.ar
);

Any ideas or suggestions would be greatly appreciated.

  • Just a bit confused: why have AND properties."TYPE_ID" IN (6); and not = 6;? – Vérace Oct 25 '17 at 7:00
  • 2
    @Vérace while = is more widly used, they both get planed the same way. My assumption is that he's playing with more than one value, or an ORM is being a tad lazy. – Evan Carroll Oct 25 '17 at 20:22
12
+100

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
    )
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.