I'm currently testing PostGIS 2.0 in a Greenplum Appliance (Postgres 8.2 based) and have hit an incompatibility between PostGIS and the MPP Greenplum environment.

The basic query below can't be processed in parallel because it's query plan uses a nested loop for the ST_Contains operator. Using a hash join would solve the problem.

SELECT pnts.gid, bdys.pid
  FROM bdys
  ON ST_Contains(bdys.geom, pnts.geom);

I've tried using the following SET commands unsuccessfully

SET enable_nestloop = OFF;
SET enable_hashjoin = ON;

Is there any way to force the query or a variant of a point in polygon query to use a hash join?

  • Did you ever solve this. Have you tried rewritting the query to: SELECT pnts.gid, bdys.pid FROM bdys, pnts WHERE ST_Contains(bdys.geom, pnts.geom); along with the set commands. – John Powell Dec 15 '14 at 8:10
  • Thanks for the idea, I gave it a try but no change unfortunately! – minus34 Dec 15 '14 at 22:45

Your Answer

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

Browse other questions tagged or ask your own question.