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
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 |