Увеличение времени выборки при добавлении индекса

[RudyM]

Windows 2000 + FB 1.0.3

Есть две таблицы:

Код: Выделить всё

CREATE TABLE CUSTOMER (      /*КРЕДИТЫ*/
    NUMBER INTEGER NOT NULL, /* номер договора*/
    DATE0 DATE,              /* дата договора */
    SUM0 FLOAT);             /* сумма кредита */
ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (NUMBER);
CREATE INDEX IDX_CUSTOMER_DATE ON CUSTOMER (DATE0);
 
CREATE TABLE PAYMENT (       /* ПОГАШЕНИЯ КРЕДИТОВ*/
    ID INTEGER NOT NULL,
    NUMBER INTEGER NOT NULL, /* номер договора */
    DATE1 DATE,              /* дата погашения */
    SUM1 FLOAT);             /* сумма погашения */
ALTER TABLE PAYMENT ADD CONSTRAINT PK_PAYMENT PRIMARY KEY (ID);

Вторя таблица связана внешним ключом с первой:

Код: Выделить всё

ALTER TABLE PAYMENT ADD  CONSTRAINT FK_PAYMENT FOREIGN KEY (NUMBER) REFERENCES CUSTOMER (NUMBER) ON DELETE CASCADE ON UPDATE CASCADE;

Мне нужна выборка: "Состояние кредитов на определенную дату", т.е. в выборке мне НЕ НУЖНЫ кредиты, заключенные после ДАТЫ, и НЕ НУЖНЫ уже погашенные кредиты. Пишу:

Код: Выделить всё

select C.NUMBER, /* номер договора */
       C.SUM0, /* сумма кредита */
       sum(P.SUM1), /* сумма погашений по кредиту */
       max(P.DATE1) /* дата последнего погашения */
  from CUSTOMER C left outer join PAYMENT P on (C.NUMBER=P.NUMBER) and
                                               (P.DATE1<='01.01.2005')
  where C.DATE0<='01.01.2005'
  group by C.NUMBER, C.SUM0
  having (C.SUM0>sum(P.SUM1)) or (sum(P.SUM1) is NULL)

Работает отлично, практически идеально (в первой таблице примерно 10 тыс. записей, во второй - 50 тыс.)

Код: Выделить всё

Query Time
------------------------------------------------
Prepare       : 10
Execute       : 1 222
Avg fetch time: 101,83 ms

Plan:
------------------------------------------------
PLAN SORT (JOIN (C INDEX (IDX_CUSTOMER_DATE),P INDEX (RDB$FOREIGN3)))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
|        Table Name        | Index | Non-Index | Updated | Deleted | Inserted |
|                          | reads |   reads   |         |         |          |
+--------------------------+-------+-----------+---------+---------+----------+
|               RDB$INDICES|     2 |         0 |       0 |       0 |        0 |
|             RDB$RELATIONS|     2 |         0 |       0 |       0 |        0 |
|                  CUSTOMER| 6 681 |         0 |       0 |       0 |        0 |
|                   PAYMENT| 40 92 |         0 |       0 |       0 |        0 |
|  RDB$RELATION_CONSTRAINTS|     0 |        12 |       0 |       0 |        0 |
+--------------------------+-------+-----------+---------+---------+----------+

Но мне показалось мало и я решил добавить индекс по полю DATE1 для таблицы PAYMENT.

Код: Выделить всё

CREATE INDEX IDX_PAYMENT_DATE ON PAYMENT (DATE1);

В результате время выполнения запроса увеличилось на порядок.

Код: Выделить всё

Query Time
------------------------------------------------
Prepare       : 20
Execute       : 38 456
Avg fetch time: 3 204,67 ms

Plan:
------------------------------------------------
PLAN SORT (JOIN (C INDEX (IDX_CUSTOMER_DATE),P INDEX (RDB$FOREIGN3,IDX_PAYMENT_DATE)))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
|        Table Name        | Index | Non-Index | Updated | Deleted | Inserted |
|                          | reads |   reads   |         |         |          |
+--------------------------+-------+-----------+---------+---------+----------+
|                  CUSTOMER| 6 681 |         0 |       0 |       0 |        0 |
|                   PAYMENT| 26 52 |         0 |       0 |       0 |        0 |
|  RDB$RELATION_CONSTRAINTS|     0 |        12 |       0 |       0 |        0 |
+--------------------------+-------+-----------+---------+---------+----------+

Пожалуйста объясните, почему?

Извитине, что так длинно

[dimitr]

У тебя LEFT JOIN, следовательно порядок соединения таблиц предопределен - сначала CUSTOMER, потом PAYMENT. Ты создаешь индекс по полю второй таблицы, который подхватывается оптимизатором. Это поле не есть связь с первой таблицей. Следовательно, в таблице CUSTOMER чтений будет столько же, но в PAYMENT может быть меньше. Что мы и наблюдаем. Но в случае плохой селективности поля DATE1 или если условие <= отбирает много записей получается, что на каждую выбранную запись из CUSTOMER будет выполнен офигенный индексный скан в PAYMENT, причем большая часть его результатов будет игнорирована, ибо выборка по FK явно более селективная. Вывод - скан по DATE1 тут явно избыточный и только ухудшает ситуацию. Такое в жизни вполне возможно, индексы далеко не всегда есть панацея.