Re: slow query question: more indexes considered harmful - Mailing list pgsql-general

Hi,

 I have typical setup consisting of two tables (demography and ses) with a typical filter-join-groupby-orderby query.

Schemas:

demography (pid int, countyid int)

ses (pid int, exposed_time int)

query:

select countyid, count(pid)

from demography, ses
where demography.pid = ses.pid

and exposed_time >4678 and exposed_time < 5042

group by countyid

order by countyid desc;

If I have indexes on all the fields pid (in both tables), countyid, exposed_time then the
query takes 21 secs.  The query plan is at http://explain.depesz.com/s/ojX

If I drop the countyid and exposed_time index then the query takes 15-16 secs.

Query plan for this is here : http://explain.depesz.com/s/vj4

I would like to keep all the indexes. But not sure what to change so that the engine
is guided towards picking up the second plan.

Thanks in advance,

Sandeep