slow query question: more indexes considered harmful

From: Sandeep Gupta <gupta(dot)sandeep(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: slow query question: more indexes considered harmful
Date: 2014-05-19 01:43:49
Message-ID: CAAywg7sD6wnxbP_A1FauBZprCLgYRhqaKKMH6cYbKaDwVBnthw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2014-05-19 06:04:17 Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION
Previous Message Tom Lane 2014-05-19 01:35:30 Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION