Индексы на больших объёмах

aev
Дата: 03.06.2004 06:18:37
Имеется БД в которой более 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) - время работы получается больше. В чём дело?
Wireless
Дата: 03.06.2004 07:59:59
I. Попробуй сделать
1. ALTER TABLE tr_email ALTER category_id SET STATISTICS 500;
2. ANALYZE tr_email
где "500" число порядка select count(distinct category_id) from tr_email

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

Ш. Если ты уверен, что оптимизатор ведет себя неверно и тебе точно
нужно использовать в этом запросе индекс, то сделай перед этим запросом
set enable_Seqscan=false;

P.S. PG какой версии ?
aev
Дата: 03.06.2004 08:21:49
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
aev
Дата: 03.06.2004 08:34:21
И ещё:
Если категория содержит менее 10 млн. записей, то используется индекс. Если больше - то нет. Так и должно быть? Время выполнения около 30 минут!!!
Wireless
Дата: 03.06.2004 08:54:23
Да, что-то медленно. Что говорят команды iostat, top во время
выполненеия запроса?
Сколько Мб физически таблица занимает?
Винты медленные, какой сервер? Могу посоветовать поставить RAID.

Создай N таблиц, где N - число категорий.
tr_email_1, tr_email_2, tr_email_3 и т.д.
И запросы соотв-м образом формировать.
Это убогий вариант того, что в Oracle называется partioning.
Индекс по полю categoty_id здесь вообще не нужен :)

И еще, пересоздай таблицу с опцией WITHOUT OIDS.
aev
Дата: 03.06.2004 10:01:51
Характеристики машины:
Dual Xeon 2.4Ghz, 2GB Ram, mirrored 80GB HDDs

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

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

Идея с созданием таблиц для каждой категории не подходит, т.к. придётся очень много кода переписывать.
Sad Spirit
Дата: 03.06.2004 10:13:29
таблица охрененно напоминает базу адресов для спама. давить ублюдков.
aev
Дата: 03.06.2004 10:20:54
если бы спам............. статистика ГТС
Wireless
Дата: 03.06.2004 10:32:24
автор
Идея с созданием таблиц для каждой категории не подходит, т.к. придётся очень много кода переписывать.

а жаль, это бы здорово помогло...

Попробуй сделать CLUSTER tr_emailndx1 ON tr_email .

Над category_id у тебя один индекс по 2м полям.
"tr_emailndx1" btree (category_id, is_active)
Посмотри не станет ли лучше если будет отдельный индекс только
по полю category.

автор
Характеристики машины:

Подключи еще один винт и перемести туда файл-индекс,
а на старом месте сделай symlink.
И подключи еще один винт, разместив там WAL,
...эээ, здесь хотел сказать временное табличное пространство, но в PG
этого нет:), но думаю в PG как-то тоже можно заставить создавать
временные файлы (кот-ые исп-ся например при больших сортировках)
в указанном месте.
Монтируй файловые системы с опцией noatime, в режиме soft updates.

И еще, если у тебя запросы в основном идут к category_id=1,
то можешь сделать условный индекс только для этой категории:
CREATE INDEX .... WHERE category_id=1
и вообще говоря, можешь сделать несколько условных индексов для
каждой категории
CREATE INDEX tr_email_categ_1 . . . . . WHERE category_id=1
CREATE INDEX tr_email_categ_2 . . . . . WHERE category_id=2
надо смотреть, тоже должно помочь для выборок,
но за счет замедления INSERT/UPDATE...
Wireless
Дата: 03.06.2004 10:34:40
> напоминает базу адресов для спама.
ну, думаю, если все заработает, aev мне сделает одну рассылку на Штаты?