Explain and Indexes

aev
Дата: 12.04.2004 12:22:51
# explain select * from emails where category_id > 2;
QUERY PLAN
------------------------------------------------------------
Seq Scan on emails (cost=0.00..22.50 rows=333 width=154)
Filter: (category_id > 2)
(записей: 2)

# explain select * from emails where category_id = 2;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using emailsndx1 on emails (cost=0.00..17.07 rows=5 width=154)
Index Cond: (category_id = 2)
(записей: 2)


Если я правильно понимаю эту информацию, то при условии 'category_id = 2' используется индекс, а при условии 'cetegory_id > 2' - нет (т.е. осуществляется полный перебор)

Почему так происходит? Индекс же есть!
LeXa NalBat
Дата: 12.04.2004 12:50:36
Попробуйте сделать vacuum analyze emails.

"(записей: 2)" - Это обозначает что в таблице две строки с category_id > 2, и две - с category_id = 2?
aev
Дата: 12.04.2004 13:02:40
автор
"(записей: 2)" - Это обозначает что в таблице две строки с category_id > 2, и две - с category_id = 2?


Нет - записей в таблице 1.000.000. Я её только что создал и не удалял и не редактировал, так что vacuum тут не причём
aev
Дата: 12.04.2004 13:25:27
А вообще какой тип индексов лучше использовать для внешних ключей?
Я читал что индексы btree лучше использовать для числовых полей и полей типа дата/время. А hash - для текстовых полей.
Также пишут что btree лучше подходит для операций <, > и сортировки, а hash - для = и <>.
Внешний ключ - число, применяется операция сравнения. Что выбрать?

Таблица будет иметь несколько миллионов записей.

И ещё несколько наболевших вопросов:
1) Правда ли что unique индексы быстрее, чем простые индексы?
2) При таких объёмах стоит ли индексировать поле типа boolen (имеется/отсутствует)? Может оно без индекса быстрее будет?
3) Стоит ли индексировать поле типа varchar(255) - наименование. По нему будет производится поиск с помощью оператора like (скорее всего на плное вхождение: '%template%')? Eсли стоит, то каким индексом? Hash?

Очень нужна помощь в этом вопросе! Буду длагодарен любым советам.
LeXa NalBat
Дата: 12.04.2004 13:26:51
"Я её только что создал и не удалял и не редактировал, так что vacuum тут не причём"

Повторюсь: попробуйте сделать vacuum analyze emails. С ключом analyze постгрес собирает статистику, кроме выполнения действий с удаленными/измененными строками.

Если это не поможет, то приведите пожалуйста результат работы двух ваших explain запросов с ключом analyze:
explain analyze select * from emails where category_id > 2;
explain analyze select * from emails where category_id = 2;
aev
Дата: 12.04.2004 13:27:31
автор
Таблица будет иметь несколько миллионов записей.

Уточнение: даже несколько десятков миллионов (от 10.000.000 до 100.000.000)
aev
Дата: 12.04.2004 13:35:24
автор
Если это не поможет, то приведите пожалуйста результат работы двух ваших explain запросов с ключом analyze:
explain analyze select * from emails where category_id > 2;
explain analyze select * from emails where category_id = 2;


# explain analyze select * from emails where category_id = 2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using emailsndx1 on emails (cost=0.00..17.07 rows=5 width=154) (actual time=180.16..41126.74 rows=10130 loops=1)
Index Cond: (category_id = 2)
Total runtime: 41150.82 msec
(записей: 3)

# explain analyze select * from emails where category_id > 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on emails (cost=0.00..22.50 rows=333 width=154) (actual time=28.41..6218.24 rows=979922 loops=1)
Filter: (category_id > 2)
Total runtime: 6955.11 msec
(записей: 3)

Вот такие результаты.
Вопрос: analyze собирает статистику для последующей оптимизации. Это значит что analyze нужно делать для всех запросов:
analyze select * from emails where category_id = 1;
analyze select * from emails where category_id = 2;
........
analyze select * from emails where category_id > 1;
analyze select * from emails where category_id > 2;
..........
или достаточно analyze select * from emails?
LeXa NalBat
Дата: 12.04.2004 13:53:14
Не путайте ключи analyze в командах vacuum и explain - у них разный смысл. В vacuum-е он обозначает сбор статистической инфы о таблице. В explain-е - реальное выполнение запроса и выдачу реальной статистики запроса наряду с предполагаемой. Подробнее смотрите в доках.

"Вопрос: analyze собирает статистику для последующей оптимизации. Это значит что analyze нужно делать для всех запросов"

Надо сделать лишь "vacuum analyze emails" или "vacuum analyze" для сбора статистики по всем таблицам в базе. Подробнее смотрите в доках.

Результаты двух "explain analyze ...", которые вы привели получены после выполнения "vacuum analyze emails"? Вы сделали "vacuum analyze emails"?
aev
Дата: 12.04.2004 14:02:24
автор
Результаты двух "explain analyze ...", которые вы привели получены после выполнения "vacuum analyze emails"? Вы сделали "vacuum analyze emails"?


После этой процедуры вот что получилось:
# explain analyze select * from emails where category_id = 2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on emails (cost=0.00..20322.01 rows=13333 width=28) (actual time=43.04..8796.18 rows=10130 loops=1)
Filter: (category_id = 2)
Total runtime: 8812.18 msec
(записей: 3)

# explain analyze select * from emails where category_id > 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on emails (cost=0.00..20322.01 rows=973914 width=28) (actual time=35.24..8336.33 rows=979922 loops=1)
Filter: (category_id > 2)
Total runtime: 9116.29 msec
(записей: 3)

Оптимизатор совсем отказался от индексирования?
LeXa NalBat
Дата: 12.04.2004 14:21:24
Как видно, теперь постгрес правильно предсказывает кол-во возвращаемых строк:
(... rows=13333 ...) (actual ... rows=10130 ...)
(... rows=973914 ...) (actual ... rows=979922 ...)

Сделайте теперь пожалуйста:
set enable_seq_scan to off;
explain analyze select * from emails where category_id = 2;
explain analyze select * from emails where category_id > 2;