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?
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);