select first 1 id from t where id>:N - разное число инд. чтений при N<=2 и N>3

Таблоид
Дата: 30.07.2012 15:07:27
hi all. LI-V2.5.2.26448

Дано:
1) таблица из 1 столбца (он же = PK) и 10 строк:
SQL> create table t2(id int primary key); commit;
SQL> insert into t2 values(1);
SQL> insert into t2 values(2);
SQL> insert into t2 values(3);
SQL> insert into t2 values(4);
SQL> insert into t2 values(5);
SQL> insert into t2 values(6);
SQL> insert into t2 values(7);
SQL> insert into t2 values(8);
SQL> insert into t2 values(9);
SQL> insert into t2 values(10);
SQL> commit;
2) запускаю трейс и ввожу для каждого из чисел {0,1,...9} команды поиска "первого бОльшего" id'шника:
SQL> select first 1 id from t2 where id>0 order by id;

ID
============
1

SQL> select first 1 id from t2 where id>1 order by id;

ID
============
2

<... skipped ...>

SQL> select first 1 id from t2 where id>9 order by id;

ID
============
10

SQL> select first 1 id from t2 where id>10 order by id;
SQL>

Результат: в трейсе вижу, что кол-во индексных чтений равно 1 для для where id>N, где N={0,1,2}, и равно 2 для N={3,4,...9}.
Почему такое происходит ?
+ trace
2012-07-30T14:45:22.2210 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 411:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>0 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 1

2012-07-30T14:45:33.1200 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 412:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>1 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 1

2012-07-30T14:45:36.7720 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 413:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>2 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 1

2012-07-30T14:45:39.4400 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 414:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>3 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:45:48.1610 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 415:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>4 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:45:50.7320 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 416:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>5 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:45:52.9170 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 417:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>6 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:45:55.5470 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 418:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>7 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:45:57.6000 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 419:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>8 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:46:03.2360 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 420:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>9 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 2

2012-07-30T14:46:08.3650 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_37, CONCURRENCY | WAIT | READ_WRITE)

Statement 421:
-------------------------------------------------------------------------------
select first 1 id from t2 where id>10 order by id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2 ORDER RDB$PRIMARY1 INDEX (RDB$PRIMARY1))
0 records fetched
0 ms, 7 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2 1
Гаджимурадов Рустам
Дата: 30.07.2012 15:47:29

Так это же правильно, что тебе не нравится?
Убери first - будет тебе не 1 индексное чтение.

Posted via ActualForum NNTP Server 1.5

Таблоид
Дата: 30.07.2012 15:57:16
Гаджимурадов Рустам
Так это же правильно, что тебе не нравится?
Разные кол-ва IR (трейс в спойлере смотрел ?).
Гаджимурадов Рустам
Убери first - будет тебе не 1 индексное чтение.
first 1 мне как раз нужен, я его не могу убрать; ожидал при выполнении
select first 1 ... from t where id > :N order by id
видеть всегда строго 1 индексное чтение, но оказалось что это не так.
Отсюда и вопрос: зачем там второе чтение делается при N>=3 ?
Гаджимурадов Рустам
Дата: 30.07.2012 16:39:23

Таблоид> Разные кол-ва IR (трейс в спойлере смотрел ?)

А, не заметил, сорри. Странно, что не 1. Еще страннее, что 2, а не 3.
Тогда жди Влада или ДЕ, я таких нюансей не помню, хотя вроде это
правилось в 2.1, IIRC.

Posted via ActualForum NNTP Server 1.5

Таблоид
Дата: 30.07.2012 17:39:30
И еще вижу странность: если заменить ascending-индекс на descending, а также поменять знак неравенства на обратный во where-условии запроса, то единичные индексные чтения (т.е. IR=1 в трейсе) будут распределены НЕ "зеркально" по отношению к предыдущему примеру.
А именно:
1. DDL
recreate table t2desc(id int); commit;
create unique descending index t2desc_idx on t2desc(id); commit;
insert into t2desc values(1);
insert into t2desc values(2);
insert into t2desc values(3);
insert into t2desc values(4);
insert into t2desc values(5);
insert into t2desc values(6);
insert into t2desc values(7);
insert into t2desc values(8);
insert into t2desc values(9);
insert into t2desc values(10);
commit;
2. Test:
SQL> select first 1 id from t2desc where id<11 order by id desc;

ID
============
10

SQL> select first 1 id from t2desc where id<10 order by id desc;

ID
============
9

SQL> select first 1 id from t2desc where id<9 order by id desc;

ID
============
8

SQL> select first 1 id from t2desc where id<2 order by id desc;

ID
============
1

SQL> select first 1 id from t2desc where id<1 order by id desc;
3. Trace:
+
2012-07-30T17:21:58.4560 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_81, CONCURRENCY | WAIT | READ_WRITE)

Statement 533:
-------------------------------------------------------------------------------
select first 1 id from t2desc where id<11 order by id desc
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2DESC ORDER T2DESC_IDX INDEX (T2DESC_IDX))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2DESC 1

2012-07-30T17:22:10.6280 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_81, CONCURRENCY | WAIT | READ_WRITE)

Statement 534:
-------------------------------------------------------------------------------
select first 1 id from t2desc where id<10 order by id desc
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2DESC ORDER T2DESC_IDX INDEX (T2DESC_IDX))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2DESC 1

2012-07-30T17:22:17.9130 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_81, CONCURRENCY | WAIT | READ_WRITE)

Statement 535:
-------------------------------------------------------------------------------
select first 1 id from t2desc where id<9 order by id desc
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2DESC ORDER T2DESC_IDX INDEX (T2DESC_IDX))
1 records fetched
0 ms, 9 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2DESC 2

2012-07-30T17:22:24.6670 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_81, CONCURRENCY | WAIT | READ_WRITE)

Statement 536:
-------------------------------------------------------------------------------
select first 1 id from t2desc where id<2 order by id desc
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2DESC ORDER T2DESC_IDX INDEX (T2DESC_IDX))
1 records fetched
0 ms, 6 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T2DESC 1

2012-07-30T17:22:29.6120 (24136:0x2aaaab47a5f8) EXECUTE_STATEMENT_FINISH
/u01/db/firebird/t2.fdb (ATT_1, SYSDBA:NONE, NONE, <internal>)
(TRA_81, CONCURRENCY | WAIT | READ_WRITE)

Statement 537:
-------------------------------------------------------------------------------
select first 1 id from t2desc where id<1 order by id desc
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T2DESC ORDER T2DESC_IDX INDEX (T2DESC_IDX))
0 records fetched -- НЕТ "симметричной" статистики, см первый пост, лог трейса для запроса "... where id>10"!
0 ms, 2 fetch(es)
Вижу в трейсах следующие различия:
1) кол-во запросов, у которых IR=1 для ascending-индекса, было равно трём (см первый пост). В то же время, аналогичное кол-во запросов для descend-индекса хотя и равно трём, но не для "первых трёх" значений id (10,9,8), а для {10,9} и затем для {1}.
2) запрос "за правую границу" листового уровня:
select first 1 id from t2 where id>10 /*10 - наибольшее значение в таблице*/ order by id;
- для asc-индекса ПРИВОДИТ к какому-то IR (и 7 фетчам). В тоже время, аналогичный запрос по descend-индексу:
select first 1 id from t2desc where id<1 /*1 - наименьшее значение в таблице*/ order by id desc;
- НЕ приводит к IR и, соотв-но, в трейсе по нему ничего нет.

Отчего это происходит ?
Кузнецов Евгений
Дата: 30.07.2012 18:36:02
CORE-2165
--
BR, Евгений
Гаджимурадов Рустам
Дата: 30.07.2012 18:49:27

Фигасе, ноябрь 2008. :)

Posted via ActualForum NNTP Server 1.5

Таблоид
Дата: 30.07.2012 19:30:43
Кузнецов Евгений,

ясно, спасибо.

Этот самый "лишний IR" приводит к удвоению времени при поиске дырок в нумерации, что проявляется уже при 32К строках - "далёкую дырку" между id=32765 и 32767 он находит только за 43 сек (машина: P-IV 3.0 GHz, RAM = 2Gb, buffers= 1024, page = 4096, база создана с нуля).

Интересно отметить еще одно.
Вот на такой таблице:
recreate table t1(id int primary key); commit;
set term ^;
execute block as
declare i int=1;
declare n int=32767;
begin
  while(i<=n) do insert into t1 values(:i) returning :i+1 into i;
end^
set term ;^
delete from t1 where id=32766;
commit;
- нижеследующий запрос, введенный в ИБЭ *БЕЗ* fetch all (т.е. "просто" по F9):
select cur_id,nxt_id
from(
select t.id cur_id,(select first 1 id from t1 x where x.id>t.id order by x.id) nxt_id
from t1 t
) t
- выдаёт на клиента 16 строк за 11 сек, однако ИБЭ показывает совершенно феерическую ложь: якобы записи отфетчились за 719 мс:
+
Query
------------------------------------------------
select cur_id,nxt_id
from(
select t.id cur_id,(select first 1 id from t1 x where x.id>t.id order by x.id) nxt_id
from t1 t
) t

Plan
------------------------------------------------
PLAN (T X ORDER RDB$PRIMARY3 INDEX (RDB$PRIMARY3))
PLAN (T T NATURAL)

Adapted Plan
------------------------------------------------
PLAN (T X ORDER INTEG_6 INDEX (INTEG_6))
PLAN (T T NATURAL)

Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 719,00 ms
Avg fetch time: 44,94 ms

Memory
------------------------------------------------
Current: 5 317 620
Max : 5 774 372
Buffers: 1 024

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 238 119
Marks : 0


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| T1| 0 | 9353 | 4680 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
Однако, трейс безжалостен:
+
Trace session ID 7 started
2012-07-30T19:13:57.0950 (612:023FA798) TRACE_INIT

SESSION_7

<... skipped ...>

2012-07-30T19:14:42.8300 (612:023FA798) EXECUTE_STATEMENT_FINISH

C:\MIX\FIREBIRD\FB25\CCC.FDB (ATT_7, SYSDBA:NONE, WIN1251, TCPv4:127.0.0.1)

C:\MIX\IBE\IBExpert.exe:1556

(TRA_206, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)



Statement 234:

-------------------------------------------------------------------------------

select cur_id,nxt_id

from(

select t.id cur_id,(select first 1 id from t1 x where x.id>t.id order by x.id) nxt_id

from t1 t

) t



^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T X ORDER RDB$PRIMARY3 INDEX (RDB$PRIMARY3))
PLAN (T T NATURAL)

4680 records fetched

11061 ms, 238111 fetch(es)



Table Natural Index Update Insert Delete Backout Purge Expunge

***************************************************************************************************************

T1 4680 9353
<... skipped ...>

Откудова ИБЭ взял время 0.719 сек ??
Гаджимурадов Рустам
Дата: 30.07.2012 23:37:39

Я в твои запросы не вчитывался, но ты несешь феерический бред.
Ибо (по памяти) запрос, ищущий дырки в 10000 (примерно) строк,
отрабатывал достаточно быстро - не помню сколько, но точно
меньше 1 секунды, и никак не 43 сек.

Posted via ActualForum NNTP Server 1.5

Таблоид
Дата: 31.07.2012 11:44:53
Гаджимурадов Рустам
Я в твои запросы не вчитывался
Пфф... опять понеслось: "я роман Пастернака не читал, но считаю" (С)
Возьми обычную рабочую станцию, параметры её см. выше, сделай копипаст и проверь. Это совсем не больно. :-)