Query not using the indexes properly.

Поиск
Список
Период
Сортировка
От Tim Uckun
Тема Query not using the indexes properly.
Дата
Msg-id 855e4dcf0910011847q2779cbdfpdff04d8b742b6270@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query not using the indexes properly.  (Chris <dmagick@gmail.com>)
Список pgsql-general
I have a pretty simple query on a pretty simple table with about 60
million records in it.

This is the query.

SELECT * FROM "changes" WHERE (id > 1935759 and company_id = 4 and
source_model_name = 'CommissionedVisit') ORDER BY id ASC LIMIT 1


The id field is the primary key. The other fields are indexed
(company_id and source_model_name).

This query takes about 30 seconds to run on a pretty beefy machine.

Here is the explain.

"Limit  (cost=0.00..7.46 rows=1 width=45) (actual
time=28799.712..28799.712 rows=0 loops=1)"
"  ->  Index Scan using changes_pkey on changes
(cost=0.00..2331939.52 rows=312519 width=45) (actual
time=28799.710..28799.710 rows=0 loops=1)"
"        Index Cond: (id > 1935759)"
"        Filter: ((company_id = 4) AND ((source_model_name)::text =
'CommissionedVisit'::text))"
"Total runtime: 28799.749 ms"


It seem to me that it's ignoring the indexes on the text fields. Is
that right?

I did a vacuum analyze on the table but that didn't seem to help at all.

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

Предыдущее
От: Preston de Guise
Дата:
Сообщение: Programming interfaces when using MD5 authentication
Следующее
От: Chris
Дата:
Сообщение: Re: Query not using the indexes properly.