Postgis query not using the spatial index

Question

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:

Answer 1

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:

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