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 9.3/2.1: