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 мс:

Однако, трейс безжалостен:

Откудова ИБЭ взял время 0.719 сек ??

Гаджимурадов Рустам

Дата: 30.07.2012 23:37:39

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

Posted via ActualForum NNTP Server 1.5

Таблоид

Дата: 31.07.2012 11:44:53

Гаджимурадов Рустам
Я в твои запросы не вчитывался

Пфф... опять понеслось: "я роман Пастернака не читал, но считаю" (С)
Возьми обычную рабочую станцию, параметры её см. выше, сделай копипаст и проверь. Это совсем не больно. :-)

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

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