Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
Задавал такой вопрос в форуме PostgreSQL. Там ответили что такой возможности там нет, но учитывая что там и predicate push down нет, а система кроссплатформенна возник принципиальный вопрос есть ли такая возможность в других СУБД.

[url=Ссылка на тот форум]https://www.sql.ru/forum/actualthread.aspx?tid=924906[/url]

CREATE TABLE A
(
key0 integer NOT NULL,
b integer, 
CONSTRAINT pk_A PRIMARY KEY (key0)
);

CREATE TABLE B
(
key0 integer NOT NULL,
x integer, 
CONSTRAINT pk_B PRIMARY KEY (key0)
);

CREATE INDEX A_b
ON A
USING btree
(b , key0 );

CREATE INDEX B_x
ON B
USING btree
(x , key0 );


INSERT INTO A (key0,b) WITH RECURSIVE gen(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM gen WHERE i < 10000) SELECT i, i % 100 + 1 FROM gen;
INSERT INTO B (key0,x) WITH RECURSIVE gen(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM gen WHERE i < 100) SELECT i, i*37 % 100 FROM gen;

EXPLAIN ANALYZE SELECT * FROM A JOIN B ON B.key0=A.b ORDER BY B.x, A.key0 LIMIT 50;


Такие запросы нужны для динамических списков с сортировкой по связанному полю. План выполнения :

"Limit  (cost=617.94..618.07 rows=50 width=16) (actual time=6.038..6.045 rows=50 loops=1)"
"  ->  Sort  (cost=617.94..642.94 rows=10000 width=16) (actual time=6.036..6.037 rows=50 loops=1)"
"        Sort Key: b.x, a.key0"
"        Sort Method: top-N heapsort  Memory: 20kB"
"        ->  Hash Join  (cost=3.25..285.75 rows=10000 width=16) (actual time=0.047..4.023 rows=10000 loops=1)"
"              Hash Cond: (a.b = b.key0)"
"              ->  Seq Scan on a  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.011..1.000 rows=10000 loops=1)"
"              ->  Hash  (cost=2.00..2.00 rows=100 width=8) (actual time=0.028..0.028 rows=100 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 4kB"
"                    ->  Seq Scan on b  (cost=0.00..2.00 rows=100 width=8) (actual time=0.003..0.011 rows=100 loops=1)"
"Total runtime: 6.092 ms"



То есть join'им таблицы, сортируем, находим верхние 50 и фиг с ними с индексами. В итоге считывается вся база.

Соответственно есть СУБД, которые догадываются бежать по B_x, для каждой записи бежать по A_b, выкидывая в результат соответствующие записи пока не наберется 50 записей?

То есть фиг с ним бы с predicate push down, его можно обойти самому проталкивая контекст внутрь (что даже эффективнее иногда использования эвристик СУБД), а вот перестроить запрос с LIMIT'ом не понятно как :( Точнее можно но это будет такая трехэтажная конструкция, что проще действительно materialize'ить.
10 мар 12, 09:38    [12220003]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Yo.!
Guest
Bond_JamesBond,

во первых объем данных слишком мал, любой нормальный оптимизатор примет решение вычитать фулсканом таблицы, т.к. это на таком объеме дешевле (пара многоблочных чтений). если увеличить размер то оракл, например, думаю и сам нестед луп применит. даже если не применит его оптимизатор всегда можно запинать хинтами и заставить применить нестед луп.
10 мар 12, 13:55    [12220485]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
CREATE INDEX
Guest
Во-первых какая у тебя версия PG и спроси в постгресовой ветке она уже поддерживает IOS?
Во-вторых о чем уже сказал йошка - слишком малый объем данных чтобы делать NLJ.
В-третьих если все таки поддержка IOS у тебя есть и хочешь использовать индексы для JOIN, то создай индекс
CREATE INDEX B_x
ON B
USING btree
(key0, x);

В-четвёртых расскажи, где у тебя в запросе предикаты, которые надо "predicate push down".
И в-пятых расшифруй "Соответственно есть СУБД, которые догадываются бежать по B_x, для каждой записи бежать по A_b, выкидывая в результат соответствующие записи пока не наберется 50 записей?"
10 мар 12, 16:21    [12220942]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
CREATE INDEX,

Специально увеличил объем до 1 миллиона записей в А и 10к в В, принципиально ничего не поменялось :

"Limit (cost=72845.26..72845.38 rows=50 width=16) (actual time=752.861..752.866 rows=50 loops=1)"
" -> Sort (cost=72845.26..75341.27 rows=998403 width=16) (actual time=752.858..752.861 rows=50 loops=1)"
" Sort Key: b.x, a.key0"
" Sort Method: top-N heapsort Memory: 20kB"
" -> Hash Join (cost=270.00..39679.03 rows=998403 width=16) (actual time=2.730..566.940 rows=1000000 loops=1)"
" Hash Cond: (a.b = b.key0)"
" -> Seq Scan on a (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.035..128.657 rows=1000000 loops=1)"
" -> Hash (cost=145.00..145.00 rows=10000 width=8) (actual time=2.684..2.684 rows=10000 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 313kB"
" -> Seq Scan on b (cost=0.00..145.00 rows=10000 width=8) (actual time=0.006..1.064 rows=10000 loops=1)"
"Total runtime: 752.969 ms"

Версия СУБД:

"PostgreSQL 9.1rc1, compiled by Visual C++ build 1500, 32-bit"

Также на 9.0 проверено, очень сомневаюсь что в минор релизе добавили index only scan, да и не совсем понимаю причем он тут.

Также не совсем понимаю чем ей поможет индекс по key0, x когда она знает что key0 primary key и для него ровно один x.

Predicate push down привел для примера фичи, которую не поддерживает postgre, но поддерживают другие СУБД. Возможно здесь аналогичный случай.

Если бы я писал вручную этот запрос то я бы грубо говоря сделал так :

(привожу в синтаксисе "файл-серверной" СУБД например Foxpro, надеюсь будет понятно)
SET INDEX TO B_x IN B
SET INDEX TO A_b IN A
SELECT B
i=0
SCAN ALL
SELECT A
SET KEY TO B.key0
SCAN ALL
i=i+1
MESSAGEBOX(A.key0) // вывод результата
IF i>50 //
RETURN
ENDIF
ENDSCAN
SELECT B
ENDSCAN

Соответственно такой подход сделал бы все максимум за 50 итераций а не за 1 миллион как в моем случае
10 мар 12, 23:11    [12222875]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
С табуляциями :

"Limit  (cost=72845.26..72845.38 rows=50 width=16) (actual time=744.371..744.377 rows=50 loops=1)"
"  ->  Sort  (cost=72845.26..75341.27 rows=998403 width=16) (actual time=744.368..744.370 rows=50 loops=1)"
"        Sort Key: b.x, a.key0"
"        Sort Method: top-N heapsort  Memory: 20kB"
"        ->  Hash Join  (cost=270.00..39679.03 rows=998403 width=16) (actual time=2.754..558.198 rows=1000000 loops=1)"
"              Hash Cond: (a.b = b.key0)"
"              ->  Seq Scan on a  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.040..124.002 rows=1000000 loops=1)"
"              ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=2.701..2.701 rows=10000 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 313kB"
"                    ->  Seq Scan on b  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.006..1.102 rows=10000 loops=1)"
"Total runtime: 744.474 ms"


SET INDEX TO B_x IN B
SET INDEX TO A_b IN A
SELECT B
i=0
SCAN ALL
     SELECT A
     SET KEY TO B.key0
     SCAN ALL
            i=i+1
            MESSAGEBOX(A.key0) // вывод результата
            IF i>50
                    RETURN
            ENDIF
      ENDSCAN
      SELECT B
ENDSCAN
10 мар 12, 23:13    [12222881]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
CREATE INDEX
Guest
Второй индекс для SMJ.
У тебя соединение по одним столбцам, один из которых не уникальный, сортировка по другим, первый из которых не уникальный.

А так какой план будет?
(обе таблицы увеличил в 100 раз, добавил индекс, и правое соединение)
CREATE TABLE A
(
key0 integer NOT NULL,
b integer, 
CONSTRAINT pk_A PRIMARY KEY (key0)
);

CREATE TABLE B
(
key0 integer NOT NULL,
x integer, 
CONSTRAINT pk_B PRIMARY KEY (key0)
);

CREATE INDEX A_b
ON A
USING btree
(b , key0 );

CREATE INDEX B_x
ON B
USING btree
(x , key0 );

CREATE INDEX B_x2
ON B
USING btree
(key0, x);


INSERT INTO A (key0,b) WITH RECURSIVE gen(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM gen WHERE i < 1000000) SELECT i, i % 100 + 1 FROM gen;
INSERT INTO B (key0,x) WITH RECURSIVE gen(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM gen WHERE i < 10000) SELECT i, i*37 % 100 FROM gen;

EXPLAIN ANALYZE SELECT * FROM A RIGHT JOIN B ON B.key0=A.b ORDER BY B.x, A.key0 LIMIT 50;
10 мар 12, 23:24    [12222921]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
CREATE INDEX,

Да в общем то такой же

"Limit  (cost=72845.26..72845.38 rows=50 width=16) (actual time=758.816..758.820 rows=50 loops=1)"
"  ->  Sort  (cost=72845.26..75341.27 rows=998403 width=16) (actual time=758.813..758.814 rows=50 loops=1)"
"        Sort Key: b.x, a.key0"
"        Sort Method: top-N heapsort  Memory: 20kB"
"        ->  Hash Right Join  (cost=270.00..39679.03 rows=998403 width=16) (actual time=2.980..569.890 rows=1000000 loops=1)"
"              Hash Cond: (a.b = b.key0)"
"              ->  Seq Scan on a  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.048..126.398 rows=1000000 loops=1)"
"              ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=2.920..2.920 rows=10000 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 313kB"
"                    ->  Seq Scan on b  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.009..1.235 rows=10000 loops=1)"
"Total runtime: 758.931 ms"
11 мар 12, 00:26    [12223116]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
Bond_JamesBond,

Жесть. Установил Oracle 11g, он даже случай

SELECT * FROM (SELECT * FROM A JOIN B ON B.key0=A.b ORDER BY B.x, B.key0) t0 WHERE rownum < 50;

не разбирает. Хотя может я что-то не так делаю, буду еще разбираться...
11 мар 12, 11:32    [12224297]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Yo.!
Guest
Bond_JamesBond
Bond_JamesBond,

Жесть. Установил Oracle 11g, он даже случай

SELECT * FROM (SELECT * FROM A JOIN B ON B.key0=A.b ORDER BY B.x, B.key0) t0 WHERE rownum < 50;

не разбирает. Хотя может я что-то не так делаю, буду еще разбираться...

все верно, фулсканить гораздо эффективней:

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02
SQL> SELECT /*+ RULE */ * from (SELECT * FROM tst.A JOIN tst.B ON B.key0=A.b ORDER BY B.x, A.key0) t0 WHERE rownum<50;
[вырезал]
49 rows selected.

Elapsed: 00:00:05.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2782528409

----------------------------------------
| Id  | Operation               | Name |
----------------------------------------
|   0 | SELECT STATEMENT        |      |
|*  1 |  COUNT STOPKEY          |      |
|   2 |   VIEW                  |      |
|*  3 |    SORT ORDER BY STOPKEY|      |
|   4 |     NESTED LOOPS        |      |
|   5 |      TABLE ACCESS FULL  | B    |
|*  6 |      INDEX RANGE SCAN   | A_B  |
----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<50)
   3 - filter(ROWNUM<50)
   6 - access("B"."KEY0"="A"."B")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      21881  consistent gets
       3074  physical reads
          0  redo size
       1806  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         49  rows processed

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02
SQL> SELECT * from (SELECT * FROM tst.A JOIN tst.B ON B.key0=A.b ORDER BY B.x, A.key0) t0 WHERE rownum<50;

[вырезал]

49 rows selected.

Elapsed: 00:00:00.99

Execution Plan
----------------------------------------------------------
Plan hash value: 1658716002

----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    49 |  2548 |       | 16026   (1)| 89:39:35 |
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
|   2 |   VIEW                  |      |  1096K|    54M|       | 16026   (1)| 89:39:35 |
|*  3 |    SORT ORDER BY STOPKEY|      |  1096K|    54M|    63M| 16026   (1)| 89:39:35 |
|*  4 |     HASH JOIN           |      |  1096K|    54M|       |  1949   (1)| 10:54:06 |
|   5 |      TABLE ACCESS FULL  | B    | 10000 |   253K|       |    22   (0)| 00:07:24 |
|   6 |      TABLE ACCESS FULL  | A    |  1096K|    27M|       |  1926   (0)| 10:46:33 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<50)
   3 - filter(ROWNUM<50)
   4 - access("B"."KEY0"="A"."B")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1953  consistent gets
       1946  physical reads
          0  redo size
       1806  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         49  rows processed


т.е. фулсканы дают в 1.5 раза меньше физических чтений и на порядок меньше логических.
11 мар 12, 14:13    [12225342]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
CREATE INDEX
Guest
Bond_JamesBond, пральна. Попробуй rownum<5 / LIMIT 5 или ещё раз в 10-100 увеличь таблицы.

Yo. А че ты покрывающий индекс по соединяемым полям не сделал для SMJ?
11 мар 12, 14:38    [12225563]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Bond_JamesBond
Member

Откуда:
Сообщений: 61
Yo.!,

Так а где использование B_x?

То есть получается что она бежит по B полностью JOIN'ит NESTED'а с A, и потом идет все та же сортировка и отрезание.

С использование B_x и A_b по определению не будет больше 50 чтений. А никакие не тысячи.

Попробовал rownum <= 1 те же яйца.
11 мар 12, 19:03    [12227548]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
впрягусь и попробую объяснить, как я понял вопрос Bond_JamesBond.

SELECT * FROM A JOIN B ON B.key0=A.b ORDER BY B.x, A.key0 LIMIT 50;

может ли какая-нибудь СУБД выполнять этот запрос таким образом:
1) по индексу B(x) выбрать все записи с минимальным значением x,
2) присоединить к ним nestedloop-ом записи из A по условию B.key0=A.b,
3) отсортировать полученный набор записей по A.key0,
4) выдать записи наружу, и проверить кол-во на лимит 50
5) если меньше лимита, вернуться к пункту 1 с условием выбирать записи с минимальным x строго больше значения x на предыдущем шаге
13 мар 12, 12:31    [12237825]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Yo.!
Guest
LeXa NalBat,

ему уже все показали и разжували в оракловой ветке
https://www.sql.ru/forum/actualthread.aspx?tid=925205
13 мар 12, 12:48    [12238012]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
50
Guest
LeXa NalBat
впрягусь и попробую объяснить, как я понял вопрос Bond_JamesBond.

SELECT * FROM A JOIN B ON B.key0=A.b ORDER BY B.x, A.key0 LIMIT 50;

может ли какая-нибудь СУБД выполнять этот запрос таким образом:
1) по индексу B(x) выбрать все 50 записи с минимальным значением x,
2) присоединить к ним nestedloop-ом записи из A по условию B.key0=A.b,
3) отсортировать полученный набор записей по B.x, A.key0,
4) выдать записи наружу, и проверить кол-во на лимит 50
5) если меньше лимита, вернуться к пункту 1 с условием выбирать записи с минимальным x строго больше значения x на предыдущем шаге
13 мар 12, 15:00    [12239582]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
все 50
нет, именно все строки с минимальным значением x, потому что присоединяющиеся строки с минимальными значениями A.key0 могут прийтись не на Ваши случайные 50 строк из B.
(также соответствующих(-ей) строк(-и) для присоединения может не найтись в A.)

B.x, A.key0
нет, достаточно отсортировать группу по A.key0, потому что все строки в группе имеют одинаковое значение B.x, и поэтому сортировка по A.key0 эквивалентна сортировке по B.x, A.key0.

PS: неужели я тоже непонятно объяснил задачу? :)
13 мар 12, 17:16    [12241242]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
50
Guest
LeXa NalBat
все 50
нет, именно все строки с минимальным значением x, потому что присоединяющиеся строки с минимальными значениями A.key0 могут прийтись не на Ваши случайные 50 строк из B.
(также соответствующих(-ей) строк(-и) для присоединения может не найтись в A.)

B.x, A.key0
нет, достаточно отсортировать группу по A.key0, потому что все строки в группе имеют одинаковое значение B.x, и поэтому сортировка по A.key0 эквивалентна сортировке по B.x, A.key0.

PS: неужели я тоже непонятно объяснил задачу? :)

А если все B.x уникальны, то как оптимальней?
14 мар 12, 03:21    [12243790]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
50
А если все B.x уникальны, то как оптимальней?
я не ставлю вопрос о выборе плана ("как оптимальней?") среди разных вариантов, ответ на который ищет планировщик (в постгресе planner).

я спрашиваю, может ли какая-нибудь СУБД исполнить (в постгресе executor) такой план запроса. (предположим, что планировщик выбрал именно его.)

(описанный мною план подошел бы и для уникальных B.x. особенность только в том, что каждый раз на первом шаге будет выбираться ровно одна строка с минимальным x. поэтому при наличии индекса A(b,key0) на втором шаге можно присоединять строки из A по этому индексу, и набор строк уже будет в нужном порядке, сортировка натретьем шаге в этом случае не нужна.)

PS:

придумал более простой пример, как мне кажется, на ту же тему.

предположим в таблице A есть индекс по полю (f1).
запрос: SELECT * FROM A ORDER BY f1, f2 LIMIT 50

может ли какая-нибудь СУБД выполнить этот запрос с использованием индекса по (f1), не пересортировывая все строки A?

например по алгоритму:
1) по индексу A(f1) выбрать все записи с минимальным значением f1,
2)
3) отсортировать полученный набор записей по A.f2,
4) выдать записи наружу, и проверить кол-во на лимит 50
5) если меньше лимита, вернуться к пункту 1 с условием выбирать записи с минимальным f1 строго больше значения f1 на предыдущем шаге
14 мар 12, 10:04    [12244233]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54799

50
А если все B.x уникальны, то как оптимальней?

В этом случае наличие A.Key0 в order by теряет смысл вместе с вопросом ТСа.

Posted via ActualForum NNTP Server 1.5

14 мар 12, 13:02    [12245611]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
Dimitry Sibiryakov
50
А если все B.x уникальны, то как оптимальней?
В этом случае наличие A.Key0 в order by теряет смысл вместе с вопросом ТСа.
нет. потому что A.b не уникальны. и к одной строке из B с уникальным B.x может присоединиться несколько строк из A, эту группу нужно отсортировать по A.key0.
14 мар 12, 14:59    [12246677]     Ответить | Цитировать Сообщить модератору
 Re: LIMIT, JOIN и использование индексов  [new]
servit
Member

Откуда: г. Кишинёв, Республика Молдова
Сообщений: 3148
Блог
Bond_JamesBond
...
То есть join'им таблицы, сортируем, находим верхние 50 и фиг с ними с индексами. В итоге считывается вся база.

Соответственно есть СУБД, которые догадываются бежать по B_x, для каждой записи бежать по A_b, выкидывая в результат соответствующие записи пока не наберется 50 записей?
...
+ Код для СУБД Caché
Class test.A Extends %Persistent
{

Index Ab On b;

Property As %Integer;

ClassMethod Fill(isSmall As %Boolean = {$$$YES})
{
  
; удаляем данные
  
do ##class(test.A).%KillExtent()
  
do ##class(test.B).%KillExtent()
  
  
; заполняем данными
  
if isSmall {
    
for i=1:1:10000 &sql(insert into test.set b=:i#100+1)
    
for i=1:1:100 &sql(insert into test.set x=:i*37#100)
  }
else{
    
set N=1000000
    
for i=1:1:N set ^test.AD(i)=$lb("",i#100+1)
    
set ^test.AD=N

    
set N=10000
    
for i=1:1:N set ^test.BD(i)=$lb("",i*37#100)
    
set ^test.BD=N

    
; строим индексы
    
do ##class(test.A).%BuildIndices(,1,1)
    
do ##class(test.B).%BuildIndices(,1,1)
  
}
  
  
; настраиваем таблицы
  
do $system.SQL.TuneTable("test.A",1,1)
  
do $system.SQL.TuneTable("test.B",1,1)
}

}


Class test.B Extends %Persistent
{

Index Bx On x;

Property As %Integer;

}
Результаты:

  • 10000/100 записей соответственно

    1) Запрос по умолчанию
    select TOP 50 * from test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.003 Секунд 615 глобальных ссылок
    Относительная стоимость = 23082 
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read index map test.B.Bx, looping on x and ID.
    
    For each row:
    
     Read index map test.A.Ab, using the given b, and looping on ID. 
     For each row: 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of ID and b. 
    

    2) Запрос с отключённым индексом Bx
    select TOP 50 * from %IGNOREINDEX test.B.Bx test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.006 Секунд 1919 глобальных ссылок
    Относительная стоимость = 24038 
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read master map test.B.IDKEY, looping on ID.
    
    For each row:
    
     Read index map test.A.Ab, using the given b, and looping on ID. 
     For each row: 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of ID and b. 
    

    3) Все индексы отключены
    select TOP 50 * from %IGNOREINDEX test.A.Ab,test.B.Bx test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.035 Секунд 40840 глобальных ссылок
    Относительная стоимость = 148012 
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read master map test.A.IDKEY, looping on ID.
    
    For each row:
    
     Read master map test.B.IDKEY, using the given idkey value. 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of b and ID. 
    

  • 1000000/10000 записей соответственно

    1) Запрос по умолчанию
    select TOP 50 * from test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.015 Секунд 10713 глобальных ссылок
    Относительная стоимость = 5605828
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read index map test.B.Bx, looping on x and ID.
    
    For each row:
    
     Read index map test.A.Ab, using the given b, and looping on ID. 
     For each row: 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of ID and b. 
    

    2) Запрос с отключённым индексом Bx
    select TOP 50 * from %IGNOREINDEX test.B.Bx test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.101 Секунд 91118 глобальных ссылок
    Относительная стоимость = 5689872
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read master map test.B.IDKEY, looping on ID.
    
    For each row:
    
     Read index map test.A.Ab, using the given b, and looping on ID. 
     For each row: 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of ID and b. 
    

    3) Все индексы отключены
    select TOP 50 * from %IGNOREINDEX test.A.Ab,test.B.Bx test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 2.941 Секунд 4000840 глобальных ссылок
    Относительная стоимость = 14412916
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read master map test.A.IDKEY, looping on ID.
    
    For each row:
    
     Read master map test.B.IDKEY, using the given idkey value. 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of b and ID. 
    

  • 10000000/100000 записей соответственно

    1) Запрос по умолчанию
    select TOP 50 * from test.JOIN test.ON B.ID=A.ORDER BY B.xA.ID
    + Быстродействие: 0.124 Секунд 102513 глобальных ссылок
    Относительная стоимость = 56835414
    Call module B, which populates temp-file A.
    
    Read temp-file A, looping on x, ID, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read index map test.B.Bx, looping on x and ID.
    
    For each row:
    
     Read index map test.A.Ab, using the given b, and looping on ID. 
     For each row: 
     Add a row to temp-file A, subscripted by x, ID, and a counter, 
     with node data of ID and b. 
    

    Caché for Windows (x86-64) 2012.2 (Build 528_1U)
  • 14 мар 12, 20:30    [12248985]     Ответить | Цитировать Сообщить модератору
     Re: LIMIT, JOIN и использование индексов  [new]
    servit
    Member

    Откуда: г. Кишинёв, Республика Молдова
    Сообщений: 3148
    Блог
    LeXa NalBat
    предположим в таблице A есть индекс по полю (f1).
    запрос: SELECT * FROM A ORDER BY f1, f2 LIMIT 50

    может ли какая-нибудь СУБД выполнить этот запрос с использованием индекса по (f1), не пересортировывая все строки A?
    Если даже план оптимизатором выбран неэффективный, ему всегда можно подсказать.
    Например:
    + Код для СУБД Caché
    Class test.C Extends %Persistent
    {

    Index idxF1 On f1;

    Property f1 As %Integer;

    Property f2 As %Integer;

    ClassMethod Fill()
    {
      
    do DISABLE^%NOJRN

      
    do ..%KillExtent()
      
      
    ; для ускорения заполнения таблицы случайными значениями для 100млн. записей использовался прямой доступ
      
    set N=100000000
      
    for i=1:1:N {
        
    set f1=$random(50000)+i
        
    set f2=$random(i)+50000
        
    set ^test.CD(i)=$lb("",f1,f2) //запись данных
        
    set ^test.CI("idxF1",f1,i)="" //запись индекса
      
    }
      
    set ^test.CD=N

      
    do ENABLE^%NOJRN

      
    do $system.SQL.TuneTable("test.C",1,1)
    }

    }
    select TOP 50 * from test.where f1 in (select TOP 50 f1 from test.order by f1order by f1,f2

    Результат для 100млн. записей:
    + Быстродействие: 0.005 Секунд 960 глобальных ссылок
    Относительная стоимость = 53603 
    Call module B, which populates temp-file C.
    
    Read temp-file C, looping on f1, f2, and a counter.
    
    For each row:
    
     Output the row. 
     
    module B 
    Read index map test.C.idxF1, looping on f1 and ID.
    
    For each row:
    
     Read index map test.C.idxF1, using the given f1, and looping on ID. 
     For each row: 
     Read master map test.C.IDKEY, using the given idkey value. 
     Check distinct values for ID using temp-file B, 
     subscripted by a hashing of these values. 
     For each distinct row: 
     Add a row to temp-file C, subscripted by f1, f2, and a counter, 
     with node data of ID, the hashing, and [value].
    
    15 мар 12, 10:48    [12251055]     Ответить | Цитировать Сообщить модератору
     Re: LIMIT, JOIN и использование индексов  [new]
    LeXa NalBat
    Member

    Откуда: Москва
    Сообщений: 2892
    servit
    LeXa NalBat
    предположим в таблице A есть индекс по полю (f1).
    запрос: SELECT * FROM A ORDER BY f1, f2 LIMIT 50

    может ли какая-нибудь СУБД выполнить этот запрос с использованием индекса по (f1), не пересортировывая все строки A?
    select TOP 50 * from test.C where f1 in (select TOP 50 f1 from test.C order by f1) order by f1,f2
    нет. такой запрос не эквивалентен исходному. например в таблице A есть 100 строк с одинаковым минимальным значением f1. исходный запрос должен выдать те 50 из этой сотни, у которых меньше значения f2. а Ваш запрос сначала выберет случайные 50 строк из этой сотни, то есть может выбрать и с бОльшими значениями f1, в этом и ошибка, а потом их упорядочит.

    PS: как обсудили в этой и других темах (в постгресовом и оракловом разделах), можно так переформулировать запрос (CTE, unnest), чтобы он стал выполняться быстро по желаемому плану или приблизительно так. но интересует именно возможность выбора и исполнения сервером БД такого быстрого плана именно для такого простого запроса, без переформулировок.
    15 мар 12, 11:16    [12251295]     Ответить | Цитировать Сообщить модератору
     Re: LIMIT, JOIN и использование индексов  [new]
    Bond_JamesBond
    Member

    Откуда:
    Сообщений: 61
    servit,

    Cache зачет тогда... и луч позора Oracle и MSSQL. Смущает только без индексов для 1млн выполнение 2,5 секунды, остальные субд справляются за 300-500мс.

    Может к Cache адаптер сделать :)... Как у них кстати с SQL compliance, в смысле поддержка WINDOW функций, RECURSIVE CTE, и наличие каких-нибудь дебильных ограничений, типа того что временную таблицу нельзя юзать 2 раза в одном запросе (помню убило меня такое ограничение в MySQL).
    15 мар 12, 11:25    [12251382]     Ответить | Цитировать Сообщить модератору
     Re: LIMIT, JOIN и использование индексов  [new]
    servit
    Member

    Откуда: г. Кишинёв, Республика Молдова
    Сообщений: 3148
    Блог
    LeXa NalBat
    ...а Ваш запрос сначала выберет случайные 50 строк из этой сотни...
    Запрос сперва выберет первые 50 минимальных уникальных значений поля f1, затем на их основе выберет все строки из таблицы с этими значениями и уже отсортирует полученные строки по полям f1 и f2, ограничив затем результат до 50.

    Приведите пример данных, так как на моих данных (я пробовал несколько комбинаций) результаты в обоих случаях совпадают.
    15 мар 12, 12:04    [12251811]     Ответить | Цитировать Сообщить модератору
     Re: LIMIT, JOIN и использование индексов  [new]
    Warstone
    Member

    Откуда:
    Сообщений: 4896
    Блог
    Bond_JamesBond,

    Если вы пользуете Коше, то пользуйте его COS(вроде-бы)... Просто то-же самое, но написанное на SQL будет в 1,5 - 2 раза медленней.
    15 мар 12, 12:27    [12252060]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Сравнение СУБД Ответить