У меня есть таблица, я работаю над этим, имеет приблизительно 3 миллиона кортежей. Это не изменяется слишком часто (несколько обновлений, или вставляет в неделю), и читается много. (Не комментируйте varchar длины 1. Я знаю, я знаю).
Column | Type | Modifiers
-------------+-----------------------+------------------------------------------------------
id | integer | not null default nextval('mytable_id_seq'::regclass)
A | character varying(5) | not null
B | character varying(16) | not null
C | character varying(3) | not null
D | character varying(1) | not null
otherdata | character varying(99) | not null
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id)
"mytable_unique_key" UNIQUE, btree (A, B, C, D)
"mytable_B_idx" btree (B)
Foreign-key constraints:
"$1" FOREIGN KEY (A, B) REFERENCES anothertable1(A, B)
"$2" FOREIGN KEY (C) REFERENCES anothertable2(C)
"$3" FOREIGN KEY (D) REFERENCES anothertable3(D)
Referenced by:
TABLE "anothertable4" CONSTRAINT "$1" FOREIGN KEY (id) REFERENCES mytable(id)
TABLE "anothertable5" CONSTRAINT "fkey_id" FOREIGN KEY (id) REFERENCES mytable(id) ON UPDATE CASCADE ON DELETE CASCADE
id
мой первичный ключ. A,B,C,D
ключ-кандидат. Оба, очевидно, однозначно определяют кортеж.
Самые частые запросы:
SELECT * FROM mytable WHERE B='foo';
- Возвратит много кортежей
SELECT * FROM mytable WHERE A='foo' AND B='bar' AND C='baz' AND D='f';
- Возвратит один кортеж.
Следовательно, почему существуют индексы на B
и A,B,C,D
.
Теперь, по любой причине, я делаю следующий запрос (И более подобный):
SELECT * FROM mytable WHERE ((A='foo' AND B='bar') OR (B='foo' AND C='bar'));
Одно поле выполняет PostgreSQL 8.4.4. Если я ОБЪЯСНЯЮ, АНАЛИЗИРУЮТ первый запрос, я получаю следующий план запросов:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mytable (cost=9.74..174.30 rows=1 width=14) (actual time=0.000..0.000 rows=5 loops=1)
Recheck Cond: ((((A)::text = 'foo'::text) AND ((B)::text = 'bar'::text)) OR ((B)::text = 'foo'::text))
Filter: ((((A)::text = 'foo'::text) AND ((B)::text = 'bar'::text)) OR (((B)::text = 'foo'::text) AND ((C)::text = 'bar'::text)))
-> BitmapOr (cost=9.74..9.74 rows=42 width=0) (actual time=0.000..0.000 rows=0 loops=1)
-> Bitmap Index Scan on mytable_unique_key(cost=0.00..4.80 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (((A)::text = 'foo'::text) AND ((B)::text = 'bar'::text))
-> Bitmap Index Scan on mytable_B_idx(cost=0.00..4.94 rows=42 width=0) (actual time=0.000..0.000 rows=316 loops=1)
Index Cond: ((B)::text = 'foo'::text)
Total runtime: 0.000 ms
(9 rows)
Минимальная стоимость 9,74 и почти мгновенный возврат (Yeh, это кэшируется). Теперь, если я выполняю тот же запрос на PostgreSQL 8.1.5 на другой подобной машине - с тем же самым содержанием в таблице - я получаю следующее:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mytable (cost=110156.34..110168.36 rows=3 width=26) (actual time=147200.984..147221.480 rows=5 loops=1)
Recheck Cond: ((((A)::text = 'foo'::text) AND ((B)::text = 'bar'::text)) OR (((B)::text = 'foo'::text) AND ((C)::text = 'bar'::text)))
-> BitmapOr (cost=110156.34..110156.34 rows=3 width=0) (actual time=147185.513..147185.513 rows=0 loops=1)
-> Bitmap Index Scan on mytable_unique_key(cost=0.00..2.01 rows=1 width=0) (actual time=83.275..83.275 rows=0 loops=1)
Index Cond: (((A)::text = 'foo'::text) AND ((B)::text = 'bar'::text))
-> Bitmap Index Scan on mytable_unique_key(cost=0.00..110154.34 rows=2 width=0) (actual time=147102.230..147102.230 rows=5 loops=1)
Index Cond: (((B)::text = 'foo'::text) AND ((C)::text = 'bar'::text))
Total runtime: 147221.663 ms
(8 rows)
Обе таблицы были VACUUM'ed и обоими полями. Так, невероятное различие происходит из-за отличающихся версий и повышений производительности, представленных между 8.1.5 и 8.4.4. Большой до devs!
Хорошо, точка этого вопроса не должна сравнивать различных версий PostgreSQL, но спросить это: Как я могу улучшить производительность вышеупомянутого запроса? У меня есть следующие решения (или вопросы):
A,B
и B,C
или A
, B
и C
? Первый поможет с вышеупомянутым запросом. Но, у меня есть другие подобные запросы, которые фильтруют на других столбцах. Запросы будут сделаны на этих наборах столбца: B
, B,C
, A,B
, A,B,C
, B,C,D
и A,B,C,D
. Это означает, что мне нужен индекс для каждого набора столбца? Или просто самое дорогое? В вышеупомянутом запросе, сканирующем для B,C
было самым дорогим.Заранее спасибо.
Похоже, что mytable_unique_key-index раздута на вашем 8.1-box. Попробуйте сначала исправить это:
REINDEX TABLE tablename;
После переиндексации, не могли бы вы сделать новый EXPLAIN?
Вам также следует начать миграцию на более новую версию, поддержка 8.1 закончится в этом году.