Добрый день господа, мой первый пост. Есть у меня проблема с которой не знаю куда и к кому обратиться.
Имеется у меня табличка на 70 миллионов записей и с каждым рабочим днем увеличивающаяся в среднем на еще миллион.
Ниже скрипт таблицы с парой индексов.

CREATE TABLE "dimam".rtorders(
id SERIAL NOT NULL,
pageid INTEGER default 1 NOT NULL,
s_marketplaceid INTEGER NOT NULL,
s_marketplaceid2 INTEGER default 0 NOT NULL,
marketplaceid INTEGER default 1000 NOT NULL,
s_actstatusid INTEGER default 1 NOT NULL,
s_vstatusid INTEGER default 1 NOT NULL,
s_rtsourceid INTEGER default 1 NOT NULL,
s_rttableid INTEGER default 1 NOT NULL,
rtsourcerecid INTEGER,
s_ordercondid INTEGER default 2 NOT NULL,
odealtype INTEGER default 0 NOT NULL,
ordernumber INTEGER,
ordernumtxt VARCHAR(40),
marketnumber DECIMAL(30,0) default 0,
clientcode VARCHAR(8),
portfolioid INTEGER default 1 NOT NULL,
portfoliocode VARCHAR(20),
tradecode VARCHAR(20),
docnumtxt VARCHAR(40),
docdate DATE,
assetcode VARCHAR(60),
assetname VARCHAR(25),
assettype VARCHAR(30),
isrepo INTEGER default 0,
orderdate DATE NOT NULL,
ordertime DATETIME YEAR to SECOND NOT NULL,
orderterm DATE NOT NULL,
cliringdate DATE NOT NULL,
nunits DECIMAL(30,8) default 0.00000000 NOT NULL,
nprice DECIMAL(30,8) default 0.00000000 NOT NULL,
ntotalsum DECIMAL(30,8) default 0.00000000 NOT NULL,
strikeprice DECIMAL(30,8) default 0.00000000,
reporate DECIMAL(30,8) default 0.00000000,
refundrate DECIMAL(30,8) default 0.00000000,
repoperiod INTEGER default 0,
currencytypeid INTEGER default 1,
orderkind INTEGER default 1 NOT NULL,
islong INTEGER default 0 NOT NULL,
signkind INTEGER default 0 NOT NULL,
signkindhand INTEGER default 0 NOT NULL,
signkindonline INTEGER default 0 NOT NULL,
stopkind INTEGER default 0 NOT NULL,
stoptransid INTEGER default 0 NOT NULL,
cldocsinstid INTEGER,
checkinid INTEGER default 0,
errorcount INTEGER default 0 NOT NULL,
errorid INTEGER default 0 NOT NULL,
errorstr VARCHAR(255),
insdate DATETIME YEAR to SECOND default Current YEAR to SECOND,
insuser VARCHAR(40) default User,
upddate DATETIME YEAR to SECOND,
upduser VARCHAR(40))
EXTENT SIZE 1999998 NEXT SIZE 1999998 LOCK MODE ROW;

ALTER TABLE "dimam".rtorders ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT "dimam".pk_rtorders;
CREATE INDEX "paveld".i_cdmp_rtorders ON "dimam".rtorders(cliringdate, marketplaceid, portfolioid);
CREATE INDEX "paveld".i_srtdc_rtorders ON "dimam".rtorders(cliringdate, s_rtsourceid);

Теперь суть проблемы. Таблица содержит биржевые заявки клиентов(portfolioid) за каждый рабочий день(cliringdate). Заявки бывают подписанные автоматически и нет(signkind 1/0). Из не подписанных заявок создаются документы(признак захвата checkinid 1).
Итак, мне необходимо найти минимальную дату за которую у некого клиента имеются не подписанные заявки для того чтобы создать документ.

Сам запрос выглядит так.
select
min(cliringdate),min(cliringdate) + (select d.countdate from cldocsdate d where d.cldocsid = 10002)
from rtorders
where
cliringdate >= "01.01.2010"
and marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
and portfolioid = 13936
and s_vstatusid = 30
and checkinid = 0
and signkind = 0;

План.
QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2011 14:04:18)
------
select
min(cliringdate), min(cliringdate) + (select d.countdate from cldocsdate d where d.cldocsid = 10002)
from rtorders
where
cliringdate >= "01.01.2010"
and marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
and portfolioid = 13936
and s_vstatusid = 30
and checkinid = 0
and signkind = 0;

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) dimam.rtorders: INDEX PATH

Filters: ((((dimam.rtorders.checkinid = 0 AND dimam.rtorders.portfolioid = 13936 ) AND dimam.rtorders.signkind = 0 ) AND dimam.rtorders.marketplaceid = ANY <subquery> ) AND dimam.rtorders.s_vstatusid = 30 )

(1) Index Name: paveld.i_srtdc_rtorders
Index Keys: cliringdate s_rtsourceid (Aggregate) (Serial, fragments: ALL)
Lower Index Filter: dimam.rtorders.cliringdate >= 01/01/2010

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 4

1) paveld.s: INDEX PATH

(1) Index Name: paveld.i_cm_printfolder
Index Keys: cldocsid marketplaceid (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: paveld.s.cldocsid = 10002

Скорость исполнения порядка 20 секунд, меняю дату "01.01.2010" на "01.01.2011" уже минуты полторы.
Что мне не понравилось в таком исполнении, в плане указан индекс по которому он начал выборку, хотя логичнее(это по мне) было бы предположить, что он не тем индексом воспользовался, так как есть составной индекс "cliringdate, marketplaceid, portfolioid" который как мне показалось должен более подходить для такого условия выборки.
Я попробовал явно указать каким индексом должен воспользоваться запрос.
select
{++ INDEX (rtorders i_cdmp_rtorders)}
min(cliringdate),min(cliringdate) + (select d.countdate from cldocsdate d where d.cldocsid = 10002)
from rtorders
where
cliringdate >= "01.01.2010"
and marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
and portfolioid = 13936
and s_vstatusid = 30
and checkinid = 0
and signkind = 0;

План
QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2011 14:15:34)
------
select
{++ INDEX (rtorders i_cdmp_rtorders)}
min(cliringdate), min(cliringdate) + (select d.countdate from cldocsdate d where d.cldocsid = 10002)
from rtorders
where
cliringdate >= "01.01.2010"
and marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
and portfolioid = 13936
and s_vstatusid = 30
and checkinid = 0
and signkind = 0;

DIRECTIVES FOLLOWED:
INDEX ( rtorders i_cdmp_rtorders )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) dimam.rtorders: INDEX PATH

Filters: ((dimam.rtorders.checkinid = 0 AND dimam.rtorders.signkind = 0 ) AND dimam.rtorders.s_vstatusid = 30 )

(1) Index Name: paveld.i_cdmp_rtorders
Index Keys: cliringdate marketplaceid portfolioid (Key-First) (Aggregate) (Serial, fragments: ALL)
Lower Index Filter: dimam.rtorders.cliringdate >= 01/01/2010
Index Key Filters: (dimam.rtorders.portfolioid = 13936 ) AND
(dimam.rtorders.marketplaceid = ANY <subquery> )

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 4

1) paveld.s: INDEX PATH

(1) Index Name: paveld.i_cm_printfolder
Index Keys: cldocsid marketplaceid (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: paveld.s.cldocsid = 10002

Но что я заметил, скорость выборки упала до 50! секунд, а если так же поменять дату на "01.01.2011" то вообще три минуты.
Собственно с построением индексов у меня беда.

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

Ikirdishonest,

- на таких обьемах стоит задуматься о фрагментации таблицы
- как часто делается update statistics?

Спасибо за комментарий.

- update statistics делается ежедневно после массовой вставки данных утром.
- ранее в свое время пробовали разносить на разные dbspaces и раскладывать по разным дискам плюс в отдельный dbspaces индексы, но что-то де то не понравилось и вернулись опять обратно. + dbspaces не позволяет обращаться по rowid, даже не помню где это используется... Но вообще попробуем поднять в отдельную DB еще раз по такой схеме.

И опять к же если вернуться к сути проблемы это не объясняет, почему informix все таки не использует правильно index, если конечно должен.

GVF112GVFdishonest,

На вскидку, можно попробовать:
- выполнить обновление статистика для таблиц/индексов.
- изменить параметры оптимизации для выполнения запроса ( DB2 optimization classes = 5)
- выполнить запрос с использованием db2batch для анализа времени выполнения.
- натравить утилиту db2advis для получение рекомендаций по индексам MQT.

PS:
db2batch - Benchmark tool command
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002043.html?resultof=%22%64%62%32%62%61%74%63%68%22%20

db2advis - DB2 design advisor command
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002452.html?resultof=%22%64%62%32%61%64%76%69%73%22%20

С уважением,
Вадим.

хм.. простите не указал что мы пользуемся IDS 11.50.FC6W4WE. Как я понимаю DB2 это нечто другое?

GVF112GVF,

да я эту статистику гоняю вдоль и поперек :)
плюс пробовал выполнять update statistics HIGH for table rtorders(cliringdate,portfolioid,marketplaceid);

GVF112GVF
Какое значение параметра OPTCOMPIND в файле ONCONFIG ? Какая версия Informix ?

С уважением,
Вадим.

IDS 11.50.FC6W4WE
OPTCOMPIND устанавливал в 0, добавляя системную переменную в систему. Что с ней что без нее все было так же.

GVF112GVF
SET ENVIRONMENT OPTCOMPIND 2
....
-- Ваш SQL ....

C уважением,
Вадим.

по умолчанию у нас такой стоит

АнатоЛойdishonest, давайте посмотрим на запрос (начнём с малого - без особых наворотов).
Например:


1. select s.marketplaceid from printfolder s where s.cldocsid = 10002 может вернуть несколько значений?
2. По cliringdate идёт поиск min. Значит желательно его иметь последним в индексе. А до него - всё то, по чему у вас наложены явные фильтры, причём в порядке эффективности - т.е. начиная с того столбца по которому данный фильтр вернёт минимальное количество значений. Это в идеале для одного запроса.
Проставьте приблизительно количество записей, которые вам вернёт запрос, если его выполянть только с ОДНИМ из нижеперечисленных условий.
Есть подозрение, что это portfolioid. если да - вам нужны индексы типа (portfolioid, cliringdate) или (portfolioid, checkinid, cliringdate) и тому подобное. Возможно даже (portfolioid, marketplaceid, checkinid, cliringdate) - просто не представляю разнообразие marketplaceid у одного клиента...
(Я не могу тоже предположить что-такое у вас s_vstatusid и signkind).


Рекомендую начать с (portfolioid, checkinid, cliringdate)
П.С.: Да кстати, что-то у меня подозрение, что у вас нет внешних ключей - и соответственно индексов по отдельно взятым столбцам. Это правда?
1) cliringdate >= "01.01.2010"
2) marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
3) portfolioid = 13936
4) s_vstatusid = 30
5) checkinid = 0
6) signkind = 0;

Выкладываю полный список индексов и референсов
CREATE INDEX "dimam".i_clc_rtorders ON "dimam".rtorders(clientcode);

CREATE INDEX "paveld".i_cdmp_rtorders ON "dimam".rtorders(cliringdate, marketplaceid, portfolioid);

CREATE INDEX "paveld".i_cldcs_rtorders ON "dimam".rtorders(cldocsinstid);

CREATE INDEX "dimam".i_cldt_rtorders ON "dimam".rtorders(orderdate);

CREATE INDEX "dimam".i_cltr_rtorders ON "dimam".rtorders(tradecode);

CREATE INDEX "paveld".i_cldmn_rtorders ON "dimam".rtorders(cliringdate, marketnumber);

CREATE INDEX "dimam".i_nnt_rtorders ON "dimam".rtorders(ordernumber, cliringdate, pageid);

CREATE INDEX "dimam".i_nt_rtorders ON "dimam".rtorders(ordernumtxt, cliringdate, pageid);

CREATE INDEX "paveld".i_pid_rtorders ON "dimam".rtorders(portfolioid);

CREATE INDEX "informix".tmp_idx ON "dimam".rtorders(marketplaceid, portfolioid);

CREATE INDEX "paveld".i_mtd_rtorders ON "dimam".rtorders(marketnumber, ordertime, orderdate, cliringdate);

CREATE INDEX "paveld".i_srtdc_rtorders ON "dimam".rtorders(cliringdate, s_rtsourceid);

CREATE INDEX "paveld".i_err_rtorders ON "dimam".rtorders(errorid);

ALTER TABLE "dimam".rtorders ADD CONSTRAINT PRIMARY KEY
(id) CONSTRAINT "dimam".pk_rtorders;

ALTER TABLE "dimam".rtorders ADD CONSTRAINT (FOREIGN KEY
(s_actstatusid) REFERENCES "dimam".s_actstatus CONSTRAINT "dimam".fk_referen_s_actst);

ALTER TABLE "dimam".rtorders ADD CONSTRAINT (FOREIGN KEY
(s_vstatusid) REFERENCES "dimam".s_vstatus CONSTRAINT "dimam".fk_referen_s_vstat);

ALTER TABLE "dimam".rtorders ADD CONSTRAINT (FOREIGN KEY
(s_rtsourceid) REFERENCES "dimam".s_rtsource CONSTRAINT "dimam".fk_referen_s_rtsou);

ALTER TABLE "dimam".rtorders ADD CONSTRAINT (FOREIGN KEY
(s_rttableid) REFERENCES "dimam".s_rttables CONSTRAINT "dimam".fk_referen_s_rtab);

ALTER TABLE "dimam".rtorders ADD CONSTRAINT (FOREIGN KEY
(s_ordercondid) REFERENCES "dimam".s_ordercond CONSTRAINT "dimam".fk_ref_s_rtocond);

s_vstatusid - поле ссылка на внешнюю таблицу где содержатся значений которые указывают на то в какой стадии обработки находится запись. 30 в данном случае что она обработана, нет ошибок и можно отправлять в документ

signkind - это константа 0/1 - признак автоматической подписи записи

1. select s.marketplaceid from printfolder s where s.cldocsid = 10002 может вернуть несколько значений?
Да, в плане приведенном выше кстати кажется указано если я не ошибаюсь Estimated # of Rows Returned: 4

2) marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002) - имеется таблица printfolder которая указывает какие площадки должны включаться в печать для указанного типа документа
индекс таблицы CREATE INDEX "paveld".i_cm_printfolder ON "paveld".printfolder(cldocsid, marketplaceid);

количество marketplaceid порядка 50 всего, для отдельно взятого документа свой набор из этого кол-ва
клиентов portfolioid порядка 10000 тысяч
собственно количество уникальный торговых можно посчитать начиная с начала 2010 до текущего момента
вроде все указал...

АнатоЛойТаки не подходит. Нужно представлять себе индекс, его структуру и попробовать поставить себя на место СУБД, чтобы делать такие выводы. Взял он (Informix), индекс (cliringdate, marketplaceid, portfolioid). Первым полем cliringdate. Про него известно, что оно > 01.01.2010. И пошёл он - пусть даже в индексе - просматривать все эти ключи, пытаясь фильтровать (у вас же IN) среди них marketplaceid... причём чтобы min найти, нужно идти по возрастанию дат, пропуская кучу signkind =1 и checkinid = 1...
Приходит понимание...
АнатоЛойКстати я мог не попасть в яблочко с (portfolioid, checkinid, cliringdate). Могут оказаться чуть более эффективными
(portfolioid, cliringdate), (portfolioid, signkind, cliringdate) или даже (portfolioid, checkinid, signkind, cliringdate)...
Сейчас буду эксперементировать...
АнатоЛойП.С.: А при дате 01.01.2011 запрос мог тормозить по следующей очевидной причине: план запроса для 2011 был не таким же, как 2010 - а вы его не просмотрели...

QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2011 15:59:52)
------
select
min(cliringdate), min(cliringdate) + (select d.countdate from cldocsdate d where d.cldocsid = 10002)
from rtorders
where
cliringdate >= "01.01.2011"
and marketplaceid in (select s.marketplaceid from printfolder s where s.cldocsid = 10002)
and portfolioid = 13936
and s_vstatusid = 30
and checkinid = 0
and signkind = 0;

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) dimam.rtorders: INDEX PATH

Filters: ((((dimam.rtorders.checkinid = 0 AND dimam.rtorders.portfolioid = 13936 ) AND dimam.rtorders.signkind = 0 ) AND dimam.rtorders.marketplaceid = ANY <subquery> ) AND dimam.rtorders.s_vstatusid = 30 )

(1) Index Name: paveld.i_srtdc_rtorders
Index Keys: cliringdate s_rtsourceid (Aggregate) (Serial, fragments: ALL)
Lower Index Filter: dimam.rtorders.cliringdate >= 01/01/2011

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 4

1) paveld.s: INDEX PATH

(1) Index Name: paveld.i_cm_printfolder
Index Keys: cldocsid marketplaceid (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: paveld.s.cldocsid = 10002

АнатоЛойdishonest, и напоследок:
при большом разнообразии запросов в приличных (больших/серьёзных/красивых) приложениях для фильтров разработчикам стоит использовать переменные вместо констант... по крайней мере так должны стараться делать разработчики. Но если вы проверяете такой запрос с переменными "вручную" (не из приложения) - проверять приходится тоже с переменными (чтобы получить идентичную реакцию сервера). Я для этого пользуюсь eSQLEditor'ом...

Пользуюсь и eSQLEditor'ом.. и InfSQL, запрос сидит в хранимой процедуре, которая понимает от какой даты ей дальше взять набор записей и присвоить созданному документу...