

/*Create a test table with 100000 xyz points*/

--  drop table p3d;
CREATE table p3d AS
SELECT ST_MakePoint(1000*random(),1000*random(), 1000*random()) geom 
FROM generate_series(1,100000) a;

ALTER TABLE p3d ADD COLUMN gid SERIAL PRIMARY KEY;



/*Create both a 3d and 2d index*/

-- DROP INDEX  p3d_geom_gist_3d;
CREATE INDEX p3d_geom_gist_3d ON p3d USING gist(geom gist_geometry_ops_nd);


-- DROP INDEX  p3d_geom_gist_2d;
CREATE INDEX p3d_geom_gist_2d ON p3d USING gist(geom);


VACUUM ANALYZE p3d;



/*Create 4 functions that uses different combinations of indexes. If there is only 2d or 3d index and not both then the two first options picks the one present, so that works.*/

/* st_3ddwithin_2d_first is ST_3DDWithin with the 2d index before the 3d index*/
CREATE OR REPLACE FUNCTION public.st_3ddwithin_2d_first(geom1 geometry, geom2 geometry, double precision)
  RETURNS boolean AS
'SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND $1 &&& ST_Expand($2,$3) AND $2 &&& ST_Expand($1,$3) AND  _ST_3DDWithin($1, $2, $3)'
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION public.st_3ddwithin(geometry, geometry, double precision)
  OWNER TO nicklas;

/* st_3ddwithin_2d_first is ST_3DDWithin with the 3d index before the 2d index*/
CREATE OR REPLACE FUNCTION public.st_3ddwithin_3d_first(geom1 geometry, geom2 geometry, double precision)
  RETURNS boolean AS
'SELECT $1 &&& ST_Expand($2,$3) AND $2 &&& ST_Expand($1,$3) AND $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND   _ST_3DDWithin($1, $2, $3)'
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION public.st_3ddwithin(geometry, geometry, double precision)
  OWNER TO nicklas;

/* st_3ddwithin_2d_first is ST_3DDWithin only using 2d index*/
CREATE OR REPLACE FUNCTION public.st_3ddwithin_2d(geom1 geometry, geom2 geometry, double precision)
  RETURNS boolean AS
'SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND _ST_3DDWithin($1, $2, $3)'
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION public.st_3ddwithin(geometry, geometry, double precision)
  OWNER TO nicklas;

/* st_3ddwithin_2d_first is ST_3DDWithin only using 3d index*/
CREATE OR REPLACE FUNCTION public.st_3ddwithin_3d(geom1 geometry, geom2 geometry, double precision)
  RETURNS boolean AS
'SELECT $1 &&& ST_Expand($2,$3) AND $2 &&& ST_Expand($1,$3) AND _ST_3DDWithin($1, $2, $3)'
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION public.st_3ddwithin(geometry, geometry, double precision)
  OWNER TO nicklas;


/*
drop function st_3ddwithin_2d_first(geometry, geometry, double precision);
drop function st_3ddwithin_3d_first(geometry, geometry, double precision);
drop function st_3ddwithin_2d(geometry, geometry, double precision);
drop function st_3ddwithin_3d(geometry, geometry, double precision);
*/

/*Only 2d index. The planner is choosing an index scan*/
EXPLAIN 
SELECT * FROM p3d a, (SELECT * FROM p3d WHERE gid = 50000) b WHERE ST_3DDWithin_2d(a.geom, b.geom, 100);

/*Only 3d index. The planner is choosing bitmap heap scan*/
EXPLAIN
SELECT * FROM p3d a, (SELECT * FROM p3d WHERE gid = 50000) b WHERE ST_3DDWithin_3d(a.geom, b.geom, 100);

/*2d before 3d. The planner is using identical as first example with only 2d*/
EXPLAIN
SELECT * FROM p3d a, (SELECT * FROM p3d WHERE gid = 50000) b WHERE ST_3DDWithin_2d_first(a.geom, b.geom, 100);

/*3d before 2d. The planner is using identical as first example with only 2d*/
EXPLAIN
SELECT * FROM p3d a, (SELECT * FROM p3d WHERE gid = 50000) b WHERE ST_3DDWithin_3d_first(a.geom, b.geom, 100);

