slow query question: more indexes considered harmful

Поиск
Список
Период
Сортировка
От Sandeep Gupta
Тема slow query question: more indexes considered harmful
Дата
Msg-id CAAywg7sD6wnxbP_A1FauBZprCLgYRhqaKKMH6cYbKaDwVBnthw@mail.gmail.com
обсуждение исходный текст
Ответы Re: slow query question: more indexes considered harmful
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION