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.