Re: slow query question: more indexes considered harmful

This went outside the purview of the mailing list.

I wanted to get some input regarding the odd behaviour of the query
planner.
Mostly out of curiosity.

This (http://explain.depesz.com/s/vj4) query plan has actual time = 17217
vs.
this one (http://explain.depesz.com/s/ojX) which has actual time = 23321

Although the planner picks the second one. What it is about the query
planner
that make it skip plans using the index on pid?.

Thanks.
Sandeep

On Sun, May 18, 2014 at 9:43 PM, Sandeep Gupta <gupta(dot)sandeep(at)gmail(dot)com>
wrote:

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