Hi all,
Looking for some advice regarding a slow query I have and indexing.
I'm using postgresql 9.1 and this is my table that has around 6800000 rows:
CREATE TABLE mytable
(
class character varying,
floor character varying,
source_id integer,
the_geom geometry
)
WITH (
OIDS=TRUE
);
INDEX idx_source_id
USING btree
(source_id);
INDEX idx_the_geom_gist
USING gist
(the_geom);
This table is constantly hit with the below query (not always the same values in the where). The only difference between queries are the values in the where clause:
SELECT the_geom,oid from mytable
WHERE
the_geom && ST_GeomFromText('POLYGON((529342.334095833 180696.221733333,529342.334095833 181533.44595,530964.336820833 181533.44595,530964.336820833 180696.221733333,529342.334095833 180696.221733333))',find_srid('','mytable','the_geom'))
AND
(floor = 'gf' AND source_id = '689' AND class = 'General')
As the table has increased in size, this query has become slower, so I made this index:
INDEX idx_floor_sourceid_class
USING btree
(floor, source_id, class);
When I run an EXPLAIN and EXPLAIN ANALYZE the query isn't using this new index.
Sometimes it uses just idx_the_geom_gist
other times it uses idx_the_geom_gist and idx_source_id
I don't understand why it's inconsistent in its use of indexes when the query is always the same structure, just different where clause values, and I don't understand why it's not using the new index either.
Would love some help with this. I'm not sure where I'm going wrong.
Thanks in advance.
