1

I've been using postgres8.3/postgis1.5 for some time, and it works well. I've moved my entire database to a new, faster machine and upgraded to postgres9.3/postgis2.1 using pg_dump/postgis_restore.pl, which also worked well. Everything else seems to be running faster, but a particular query is now taking extremely long to complete - so naturally I checked that it was correctly indexed and the tables were vacuum+analyzed. VACUUM ANALYZE public.places (the_geom) doesn't change the result. Using EXPLAIN, I see that the 9.3/2.1 instance isn't even using a spatial index.

The public.places table is identical in both databases; same structure and exactly the same records (236,884 rows).

I don't doubt that I can write a more efficient query, but before I launch into that I'd like to at least get it running as fast as it was in 8.3/1.5 (700ms vs 580000ms). Suggestions? Does anyone see my error? What has changed in postgis2.1 that keeps the spatial index from being utilized in this query?

The sql:

SELECT max(foo.pname) as pname, split_part(attribute, '|', 2) as zipcode FROM places, 
(SELECT features.pname as pname, ST_Buffer(the_geom, -.001) as the_geom FROM places p, 
    (SELECT pname, pid FROM search WHERE gid = '50873' LIMIT 1) as features 
WHERE p.pid = ANY(features.pid)) as foo 
WHERE places.ptype = 'Zip Code Areas' 
AND ST_Intersects(places.the_geom, foo.the_geom) 
GROUP BY zipcode 
ORDER BY zipcode;

The table schema:

CREATE TABLE public.places
(
  pid integer NOT NULL DEFAULT nextval('places_pid_seq'::regclass),
  ptype text,
  pname text,
  the_geom geometry,
  sid integer NOT NULL,
  pcountry text,
  woeid integer,
  attribute text,
  CONSTRAINT places_pkey PRIMARY KEY (pid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.places OWNER TO xxxx;
GRANT ALL ON TABLE public.places TO xxxx;
GRANT SELECT ON TABLE public.places TO xxx;

CREATE INDEX places_pcountry_idx ON public.places
  USING btree (pcountry COLLATE pg_catalog."default");

CREATE INDEX places_ptype_idx ON public.places
  USING btree (pcountry COLLATE pg_catalog."default");

CREATE INDEX places_ptype_key ON public.places
  USING btree (ptype COLLATE pg_catalog."default");

CREATE INDEX places_spatial_index ON public.places
  USING gist (the_geom);

CREATE INDEX places_woeid_idx ON public.places
  USING btree (woeid);

EXPLAIN 8.3/1.5: EXPLAIN 8.3/1.5

EXPLAIN 9.3/2.1: EXPLAIN 9.3/2.1

0

I was able to force the query plan to use the spatial index by re-writing the query using the WITH expression, as suggested in a related question, PostGIS Intermittent INDEX Performance.

Here's the new sql:

WITH features AS (
    SELECT pname, pid FROM search WHERE gid = '50873' LIMIT 1
    ), thefeature AS
    (
    SELECT f.pname as pname, ST_Buffer(the_geom, -.001) as the_geom FROM places p, features f
    WHERE p.pid = ANY(f.pid)
    )

SELECT max(thefeature.pname) as pname, split_part(attribute, '|', 2) as zipcode FROM places, thefeature
WHERE places.ptype = 'Zip Code Areas' 
AND ST_Intersects(places.the_geom, thefeature.the_geom) 
GROUP BY zipcode ORDER BY zipcode;

And the resulting query plan: EXPLAIN using WITH

Obviously, lots better (114ms is six times faster than the old query in PostGIS 1.5). Problem solved.

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.