Имеется БД в которой более 60 млн. записей.

Table "public.tr_email"
Column | Type | Modifiers
-------------+------------------------+----------------------------------------------------------
id | integer | not null default nextval('public.tr_email_id_seq'::text)
category_id | integer | not null
address | character varying(255) | not null
is_active | boolean | not null default true
Indexes:
"tr_email_pkey" primary key, btree (id)
"tr_email_address_key" unique, btree (address)
"tr_emailndx1" btree (category_id, is_active)
Foreign-key constraints:
"$1" FOREIGN KEY (category_id) REFERENCES tr_email_category(id) ON DELETE CASCADE

Почему простой select (приведён ниже) не использует индекс?

EXPLAIN SELECT * FROM tr_email WHERE category_id = 1;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on tr_email (cost=0.00..1327459.55 rows=12259563 width=33)
Filter: (category_id = 1)
(2 rows)

VACUUM ANALYZE делал. Пробовал принудительно использовать индекс (SET enable_seqscan TO OFF) - время работы получается больше. В чём дело?

select count(distinct category_id) from tr_email
Число категории невелико (максимум 25)

Если SELECT count(*) FROM tr_email WHERE category_id = 1;
составляет значительную часть от общего числа записей из tr_email,
то seq_scan может быть действительно быстрее indexscan.


Всего: 64 млн. записей, и 10 категориий. Распределение такое:
1) 12,2 млн
2) 1,3 млн
3) 23 млн
4) 2,3 млн
5) 13,6 млн
6) 43 тыс
7) 590 тыс
8) 10,2 млн
9) 900 тыс

Когда я отключал seqscan, то оптимизатор показывал большее время выполнения запроса.

PostgreSQL 7.4

И ещё:
Если категория содержит менее 10 млн. записей, то используется индекс. Если больше - то нет. Так и должно быть? Время выполнения около 30 минут!!!

Характеристики машины:
Dual Xeon 2.4Ghz, 2GB Ram, mirrored 80GB HDDs

Под Shared Memory я выделил 256 MB.
Top показывает, что при выполнении selecta от процессора используется 10-30%, от памяти 13% (т.е. вся выделенная под Shared Memory)

БД занимает 9GB (все таблицы кроме данной пустые)

Идея с созданием таблиц для каждой категории не подходит, т.к. придётся очень много кода переписывать.

если бы спам............. статистика ГТС

SET enable_seqscan TO on;
EXPLAIN ANALYZE SELECT * FROM tr_email WHERE category_id = 1;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on tr_email (cost=0.00..1327459.55 rows=12259563 width=33) (actual time=0.113..2717358.453 rows=12189810 loops=1)
Filter: (category_id = 1)
Total runtime: 2731638.130 ms

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM tr_email WHERE category_id = 1;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tr_emailndx1 on tr_email (cost=0.00..34687630.64 rows=12259563 width=33) (actual time=38.962..245449.002 rows=12189810 loops=1)
Index Cond: (category_id = 1)
Total runtime: 281099.570 ms

Получается так что оптимизатор неправильно выбирает план запроса. Как сделать чтобы он использовал индекс (без принудительного enable_seqscan to off)?

Еще вопрос: что вы далее делаете с результатом запроса, состоящим из 12 миллионов строк? Если еще как-то фильтруете, то может быть сделать эту фильтрацию в самом запросе? Если вычисляете какие-то групповые функции, то может быть их вычислить в запросе?

Здесь должны производиться кое-какие подсчёты и сбор статистики, но это не важно - select подправится. Сейчас вопрос стотит так, почему запрпос по индексированному полю занимает так много времени?!

Почему же неправильно. Для этого запроса он поступил правильно, хотя бы потому что 2731638.130 ms < 281099.570 ms.

2 731 638.130 ms > 281 099.570 ms

Наверное порядком промазал..............

Я же привёл пример того что говорит explain analyze (см. чуть выше)