Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Не получается добиться Index Range Scan, может кто подскажет

Добрый вечер, уважаемые коллеги!

Oracle EE 11.2.0.4.1

Приведу упрощенный пример. Есть вьюха (несколько миллиардов строк).
Имеем вот такой запрос к вьюхе, работает без проблем (IRS)
explain plan for
select *
from codlog_all t2
where t2.MESSAGE_DEMAND_ID in ('bd0eff00d7177c4beed3dca0ad5d3bad')

select * from table( dbms_xplan.display() )
+
Plan hash value: 34159560

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                               |     5 | 25770 |  1225   (0)| 00:00:15 |       |       |
|   1 |  VIEW                                   | CODLOG_ALL                    |     5 | 25770 |  1225   (0)| 00:00:15 |       |       |
|   2 |   UNION-ALL                             |                               |       |       |            |          |       |       |
|   3 |    VIEW                                 | CODLOG                        |     2 | 10268 |   452   (0)| 00:00:06 |       |       |
|   4 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ALL                |                               |     1 |  2592 |   130   (0)| 00:00:02 |     1 |1048575|
|   6 |       PARTITION HASH ALL                |                               |     1 |  2592 |   130   (0)| 00:00:02 |     1 |    16 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| CODLOG_B                      |     1 |  2592 |   130   (0)| 00:00:02 |     1 |1048575|
|*  8 |         INDEX RANGE SCAN                | CL_MESSAGE_DEMAND_ID_INDEX_B  |     1 |       |   129   (0)| 00:00:02 |     1 |1048575|
|   9 |      PARTITION RANGE ALL                |                               |     1 |  1860 |   322   (0)| 00:00:04 |     1 |1048575|
|  10 |       PARTITION HASH ALL                |                               |     1 |  1860 |   322   (0)| 00:00:04 |     1 |    32 |
|  11 |        TABLE ACCESS BY LOCAL INDEX ROWID| CODLOG_S                      |     1 |  1860 |   322   (0)| 00:00:04 |     1 |1048575|
|* 12 |         INDEX RANGE SCAN                | CL_MESSAGE_DEMAND_ID_INDEX_S  |     1 |       |   321   (0)| 00:00:04 |     1 |1048575|
|  13 |    VIEW                                 | FINANCIAL_CODLOG              |     1 |  1645 |     1   (0)| 00:00:01 |       |       |
|  14 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|  15 |      PARTITION RANGE ALL                |                               |     1 |  1883 |   195   (0)| 00:00:03 |     1 |1048575|
|  16 |       PARTITION HASH ALL                |                               |     1 |  1883 |   195   (0)| 00:00:03 |     1 |    16 |
|  17 |        TABLE ACCESS BY LOCAL INDEX ROWID| FINANCIAL_CODLOG_B            |     1 |  1883 |   195   (0)| 00:00:03 |     1 |1048575|
|* 18 |         INDEX RANGE SCAN                | FCL_MESSAGE_DEMAND_ID_INDEX_B |     1 |       |   193   (0)| 00:00:03 |     1 |1048575|
|  19 |      PARTITION RANGE ALL                |                               |     1 |  1631 |   226   (0)| 00:00:03 |     1 |1048575|
|  20 |       PARTITION HASH ALL                |                               |     1 |  1631 |   226   (0)| 00:00:03 |     1 |    16 |
|  21 |        TABLE ACCESS BY LOCAL INDEX ROWID| FINANCIAL_CODLOG_S            |     1 |  1631 |   226   (0)| 00:00:03 |     1 |1048575|
|* 22 |         INDEX RANGE SCAN                | FCL_MESSAGE_DEMAND_ID_INDEX_S |     1 |       |   225   (0)| 00:00:03 |     1 |1048575|
|  23 |    VIEW                                 | TRASH_CODLOG                  |     2 | 10242 |   772   (0)| 00:00:10 |       |       |
|  24 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|  25 |      PARTITION RANGE ALL                |                               |     1 |  1584 |   322   (0)| 00:00:04 |     1 |1048575|
|  26 |       PARTITION HASH ALL                |                               |     1 |  1584 |   322   (0)| 00:00:04 |     1 |    32 |
|  27 |        TABLE ACCESS BY LOCAL INDEX ROWID| TRASH_CODLOG_S                |     1 |  1584 |   322   (0)| 00:00:04 |     1 |1048575|
|* 28 |         INDEX RANGE SCAN                | TCL_MESSAGE_DEMAND_ID_INDEX_S |     1 |       |   321   (0)| 00:00:04 |     1 |1048575|
|  29 |      PARTITION RANGE ALL                |                               |     1 |  1971 |   450   (0)| 00:00:06 |     1 |1048575|
|  30 |       PARTITION HASH ALL                |                               |     1 |  1971 |   450   (0)| 00:00:06 |     1 |    32 |
|  31 |        TABLE ACCESS BY LOCAL INDEX ROWID| TRASH_CODLOG_B                |     1 |  1971 |   450   (0)| 00:00:06 |     1 |1048575|
|* 32 |         INDEX RANGE SCAN                | TCL_MESSAGE_DEMAND_ID_INDEX_B |     1 |       |   449   (0)| 00:00:06 |     1 |1048575|
-----------------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  12 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  18 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  22 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  28 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  32 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')

далее вставляем подзапрос (и появляется IFFS)
explain plan for
select t.MESSAGE_DEMAND_ID
  from codlog_all t
where t.MESSAGE_DEMAND_ID in (select 'bd0eff00d7177c4beed3dca0ad5d3bad' from dual)


select * from table( dbms_xplan.display() )
+
Plan hash value: 3393341775

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                               |    44M|  1768M|  7647K  (1)| 25:29:29 |       |       |
|*  1 |  FILTER                     |                               |       |       |            |          |       |       |
|   2 |   VIEW                      | CODLOG_ALL                    |  4416M|   172G|  7647K  (1)| 25:29:29 |       |       |
|   3 |    UNION-ALL                |                               |       |       |            |          |       |       |
|   4 |     VIEW                    | CODLOG                        |  2217M|    45G|  3800K  (1)| 12:40:03 |       |       |
|   5 |      UNION-ALL              |                               |       |       |            |          |       |       |
|   6 |       PARTITION RANGE ALL   |                               |    61M|  1282M|   106K  (1)| 00:21:20 |     1 |1048575|
|   7 |        PARTITION HASH ALL   |                               |    61M|  1282M|   106K  (1)| 00:21:20 |     1 |    16 |
|   8 |         INDEX FAST FULL SCAN| CL_MESSAGE_DEMAND_ID_INDEX_B  |    61M|  1282M|   106K  (1)| 00:21:20 |     1 |1048575|
|   9 |       PARTITION RANGE ALL   |                               |  2156M|    36G|  3693K  (1)| 12:18:43 |     1 |1048575|
|  10 |        PARTITION HASH ALL   |                               |  2156M|    36G|  3693K  (1)| 12:18:43 |     1 |    32 |
|  11 |         INDEX FAST FULL SCAN| CL_MESSAGE_DEMAND_ID_INDEX_S  |  2156M|    36G|  3693K  (1)| 12:18:43 |     1 |1048575|
|  12 |     VIEW                    | FINANCIAL_CODLOG              |  1688M|    66G|  3185K  (1)| 10:37:04 |       |       |
|  13 |      UNION-ALL              |                               |       |       |            |          |       |       |
|  14 |       PARTITION RANGE ALL   |                               |    84M|  2672M|   150K  (1)| 00:30:05 |     1 |1048575|
|  15 |        PARTITION HASH ALL   |                               |    84M|  2672M|   150K  (1)| 00:30:05 |     1 |    16 |
|  16 |         INDEX FAST FULL SCAN| FCL_MESSAGE_DEMAND_ID_INDEX_B |    84M|  2672M|   150K  (1)| 00:30:05 |     1 |1048575|
|  17 |       PARTITION RANGE ALL   |                               |  1603M|    49G|  3034K  (1)| 10:06:59 |     1 |1048575|
|  18 |        PARTITION HASH ALL   |                               |  1603M|    49G|  3034K  (1)| 10:06:59 |     1 |    16 |
|  19 |         INDEX FAST FULL SCAN| FCL_MESSAGE_DEMAND_ID_INDEX_S |  1603M|    49G|  3034K  (1)| 10:06:59 |     1 |1048575|
|  20 |     VIEW                    | TRASH_CODLOG                  |   510M|    10G|   661K  (1)| 02:12:23 |       |       |
|  21 |      UNION-ALL              |                               |       |       |            |          |       |       |
|  22 |       PARTITION RANGE ALL   |                               |   486M|  3709M|   632K  (1)| 02:06:29 |     1 |1048575|
|  23 |        PARTITION HASH ALL   |                               |   486M|  3709M|   632K  (1)| 02:06:29 |     1 |    32 |
|  24 |         INDEX FAST FULL SCAN| TCL_MESSAGE_DEMAND_ID_INDEX_S |   486M|  3709M|   632K  (1)| 02:06:29 |     1 |1048575|
|  25 |       PARTITION RANGE ALL   |                               |    24M|   187M| 29534   (1)| 00:05:55 |     1 |1048575|
|  26 |        PARTITION HASH ALL   |                               |    24M|   187M| 29534   (1)| 00:05:55 |     1 |    32 |
|  27 |         INDEX FAST FULL SCAN| TCL_MESSAGE_DEMAND_ID_INDEX_B |    24M|   187M| 29534   (1)| 00:05:55 |     1 |1048575|
|* 28 |   FILTER                    |                               |       |       |            |          |       |       |
|  29 |    FAST DUAL                |                               |     1 |       |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE :B1='bd0eff00d7177c4beed3dca0ad5d3bad'))
  28 - filter(:B1='bd0eff00d7177c4beed3dca0ad5d3bad')

Видим, что появился фильтр. Лечим его хинтом:
explain plan for
select *
from codlog_all t2
where t2.MESSAGE_DEMAND_ID in
( select /*+ precompute_subquery */
'bd0eff00d7177c4beed3dca0ad5d3bad' from dual)

select * from table( dbms_xplan.display() )
+
Plan hash value: 34159560

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                               |     6 | 30924 |  1646   (1)| 00:00:20 |       |       |
|   1 |  VIEW                                   | CODLOG_ALL                    |     6 | 30924 |  1646   (1)| 00:00:20 |       |       |
|   2 |   UNION-ALL                             |                               |       |       |            |          |       |       |
|   3 |    VIEW                                 | CODLOG                        |     2 | 10268 |   452   (0)| 00:00:06 |       |       |
|   4 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ALL                |                               |     1 |  2592 |   130   (0)| 00:00:02 |     1 |1048575|
|   6 |       PARTITION HASH ALL                |                               |     1 |  2592 |   130   (0)| 00:00:02 |     1 |    16 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| CODLOG_B                      |     1 |  2592 |   130   (0)| 00:00:02 |     1 |1048575|
|*  8 |         INDEX RANGE SCAN                | CL_MESSAGE_DEMAND_ID_INDEX_B  |     1 |       |   129   (0)| 00:00:02 |     1 |1048575|
|   9 |      PARTITION RANGE ALL                |                               |     1 |  1860 |   322   (0)| 00:00:04 |     1 |1048575|
|  10 |       PARTITION HASH ALL                |                               |     1 |  1860 |   322   (0)| 00:00:04 |     1 |    32 |
|  11 |        TABLE ACCESS BY LOCAL INDEX ROWID| CODLOG_S                      |     1 |  1860 |   322   (0)| 00:00:04 |     1 |1048575|
|* 12 |         INDEX RANGE SCAN                | CL_MESSAGE_DEMAND_ID_INDEX_S  |     1 |       |   321   (0)| 00:00:04 |     1 |1048575|
|  13 |    VIEW                                 | FINANCIAL_CODLOG              |     2 | 10242 |   421   (0)| 00:00:06 |       |       |
|  14 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|  15 |      PARTITION RANGE ALL                |                               |     1 |  1883 |   195   (0)| 00:00:03 |     1 |1048575|
|  16 |       PARTITION HASH ALL                |                               |     1 |  1883 |   195   (0)| 00:00:03 |     1 |    16 |
|  17 |        TABLE ACCESS BY LOCAL INDEX ROWID| FINANCIAL_CODLOG_B            |     1 |  1883 |   195   (0)| 00:00:03 |     1 |1048575|
|* 18 |         INDEX RANGE SCAN                | FCL_MESSAGE_DEMAND_ID_INDEX_B |     1 |       |   193   (0)| 00:00:03 |     1 |1048575|
|  19 |      PARTITION RANGE ALL                |                               |     1 |  1631 |   226   (0)| 00:00:03 |     1 |1048575|
|  20 |       PARTITION HASH ALL                |                               |     1 |  1631 |   226   (0)| 00:00:03 |     1 |    16 |
|  21 |        TABLE ACCESS BY LOCAL INDEX ROWID| FINANCIAL_CODLOG_S            |     1 |  1631 |   226   (0)| 00:00:03 |     1 |1048575|
|* 22 |         INDEX RANGE SCAN                | FCL_MESSAGE_DEMAND_ID_INDEX_S |     1 |       |   225   (0)| 00:00:03 |     1 |1048575|
|  23 |    VIEW                                 | TRASH_CODLOG                  |     2 | 10242 |   772   (0)| 00:00:10 |       |       |
|  24 |     UNION-ALL                           |                               |       |       |            |          |       |       |
|  25 |      PARTITION RANGE ALL                |                               |     1 |  1584 |   322   (0)| 00:00:04 |     1 |1048575|
|  26 |       PARTITION HASH ALL                |                               |     1 |  1584 |   322   (0)| 00:00:04 |     1 |    32 |
|  27 |        TABLE ACCESS BY LOCAL INDEX ROWID| TRASH_CODLOG_S                |     1 |  1584 |   322   (0)| 00:00:04 |     1 |1048575|
|* 28 |         INDEX RANGE SCAN                | TCL_MESSAGE_DEMAND_ID_INDEX_S |     1 |       |   321   (0)| 00:00:04 |     1 |1048575|
|  29 |      PARTITION RANGE ALL                |                               |     1 |  1971 |   450   (0)| 00:00:06 |     1 |1048575|
|  30 |       PARTITION HASH ALL                |                               |     1 |  1971 |   450   (0)| 00:00:06 |     1 |    32 |
|  31 |        TABLE ACCESS BY LOCAL INDEX ROWID| TRASH_CODLOG_B                |     1 |  1971 |   450   (0)| 00:00:06 |     1 |1048575|
|* 32 |         INDEX RANGE SCAN                | TCL_MESSAGE_DEMAND_ID_INDEX_B |     1 |       |   449   (0)| 00:00:06 |     1 |1048575|
-----------------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  12 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  18 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  22 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  28 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')
  32 - access("MESSAGE_DEMAND_ID"='bd0eff00d7177c4beed3dca0ad5d3bad')

далее пробуем расширить условие:
explain plan for
select *
from codlog_all t2
where t2.MESSAGE_DEMAND_ID in
( select /*+ precompute_subquery */
'bd0eff00d7177c4beed3dca0ad5d3bad' from dual union all select '7098edbff730cfbfce09441b88bcbd08' from dual)

select * from table( dbms_xplan.display() )
+
Plan hash value: 4228117697

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |    88M|   423G|   252M  (1)|841:46:05 |       |       |
|*  1 |  FILTER                  |                    |       |       |            |          |       |       |
|   2 |   VIEW                   | CODLOG_ALL         |  4416M|    20T|   252M  (1)|841:46:05 |       |       |
|   3 |    UNION-ALL             |                    |       |       |            |          |       |       |
|   4 |     VIEW                 | CODLOG             |  2217M|    10T|   138M  (1)|460:10:09 |       |       |
|   5 |      UNION-ALL           |                    |       |       |            |          |       |       |
|   6 |       PARTITION RANGE ALL|                    |    61M|   147G|  5478K  (1)| 18:15:42 |     1 |1048575|
|   7 |        PARTITION HASH ALL|                    |    61M|   147G|  5478K  (1)| 18:15:42 |     1 |    16 |
|   8 |         TABLE ACCESS FULL| CODLOG_B           |    61M|   147G|  5478K  (1)| 18:15:42 |     1 |1048575|
|   9 |       PARTITION RANGE ALL|                    |  2156M|  3734G|   132M  (1)|441:54:28 |     1 |1048575|
|  10 |        PARTITION HASH ALL|                    |  2156M|  3734G|   132M  (1)|441:54:28 |     1 |    32 |
|  11 |         TABLE ACCESS FULL| CODLOG_S           |  2156M|  3734G|   132M  (1)|441:54:28 |     1 |1048575|
|  12 |     VIEW                 | FINANCIAL_CODLOG   |  1688M|  8050G|    88M  (1)|293:44:03 |       |       |
|  13 |      UNION-ALL           |                    |       |       |            |          |       |       |
|  14 |       PARTITION RANGE ALL|                    |    84M|   148G|  5293K  (1)| 17:38:42 |     1 |1048575|
|  15 |        PARTITION HASH ALL|                    |    84M|   148G|  5293K  (1)| 17:38:42 |     1 |    16 |
|  16 |         TABLE ACCESS FULL| FINANCIAL_CODLOG_B |    84M|   148G|  5293K  (1)| 17:38:42 |     1 |1048575|
|  17 |       PARTITION RANGE ALL|                    |  1603M|  2435G|    82M  (1)|276:05:22 |     1 |1048575|
|  18 |        PARTITION HASH ALL|                    |  1603M|  2435G|    82M  (1)|276:05:22 |     1 |    16 |
|  19 |         TABLE ACCESS FULL| FINANCIAL_CODLOG_S |  1603M|  2435G|    82M  (1)|276:05:22 |     1 |1048575|
|  20 |     VIEW                 | TRASH_CODLOG       |   510M|  2436G|    26M  (1)| 87:51:54 |       |       |
|  21 |      UNION-ALL           |                    |       |       |            |          |       |       |
|  22 |       PARTITION RANGE ALL|                    |   486M|   717G|    24M  (1)| 82:06:53 |     1 |1048575|
|  23 |        PARTITION HASH ALL|                    |   486M|   717G|    24M  (1)| 82:06:53 |     1 |    32 |
|  24 |         TABLE ACCESS FULL| TRASH_CODLOG_S     |   486M|   717G|    24M  (1)| 82:06:53 |     1 |1048575|
|  25 |       PARTITION RANGE ALL|                    |    24M|    45G|  1725K  (1)| 05:45:01 |     1 |1048575|
|  26 |        PARTITION HASH ALL|                    |    24M|    45G|  1725K  (1)| 05:45:01 |     1 |    32 |
|  27 |         TABLE ACCESS FULL| TRASH_CODLOG_B     |    24M|    45G|  1725K  (1)| 05:45:01 |     1 |1048575|
|  28 |   UNION-ALL              |                    |       |       |            |          |       |       |
|* 29 |    FILTER                |                    |       |       |            |          |       |       |
|  30 |     FAST DUAL            |                    |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 31 |    FILTER                |                    |       |       |            |          |       |       |
|  32 |     FAST DUAL            |                    |     1 |       |     2   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT /*+ PRECOMPUTE_SUBQUERY */ 'bd0eff00d7177c4beed3dca0ad5d3bad' FROM 
              "SYS"."DUAL" "DUAL" WHERE :B1='bd0eff00d7177c4beed3dca0ad5d3bad') UNION ALL  (SELECT 
              '7098edbff730cfbfce09441b88bcbd08' FROM "SYS"."DUAL" "DUAL" WHERE 
              :B2='7098edbff730cfbfce09441b88bcbd08')))
  29 - filter(:B1='bd0eff00d7177c4beed3dca0ad5d3bad')
  31 - filter(:B1='7098edbff730cfbfce09441b88bcbd08')

и получаем вовсе FTS. Что только не перепробовал - не могу добиться IRS, если в условии IN несколько значений. Писать PL\SQL под это хозяйство не хотелось бы.
Пробовал в частности старый добрый WITH (c materialize) и в связке с Cardinality, No_unnest. Ничего не помогает. Может есть какие-нибудь идеи?
Ответ:
Вячеслав Любомудров
То, что у ТС 1M секций в каждой таблице никто не обратил внимания?
Сдается мне, трудновато это прожевать оптимизатору. А если там еще и глистограммы...

По-видимому так и есть - выяснили, что оптимизатору тяжело пушить предикаты к таблицам сквозь сложные вьюхи (в оригинальном запросе задействованы вьюхи с уровнем вложенности 2 и 3). Как только убираем вьюхи и вставляем вместо них таблицы - получаем желанные IRS.
Вопрос: index unique scan после удаления статистики

Здравствуйте!
Помогите, пожалуйста, со следующей проблемой:
1.
Есть таблица t с полями f1(primary key, number),f2(number),f3(varchar2),f4(varchar2),f5,f6,f7.
Имеются отдельные индексы по полям f2,f3,f4.
Количество записей в таблице ~8 млн.
В среднем одна запись таблицы содержит ~1500 символов во всех полях вместе взятых.
2.
Удаляю статистику по таблице t: "exec sys.dbms.delete_table_statistics('myschema','t')",
удаляю статистику по индексам:
"exec sys.dbms.delete_index_statistics('myschema','t_f2_ix2')",
"exec sys.dbms.delete_index_statistics('myschema','t_f3_ix3')",
"exec sys.dbms.delete_index_statistics('myschema','t_f4_ix4')".

3.
Выполняю скрипт:
SELECT *
FROM t
WHERE f2=2 AND f3=3 AND f4=4

4.
В результате в explain plan "INDEX RANGE SCAN", хотя я ожидал увидеть "table full scan". Или я неправильно ожидал?
Ответ: Такой же эксперимент проводился на БД с той же самой структурой, с объемом данных того же порядка, но на другом физическом сервере (версия такая же - 11).
И там в плане выполнения предлагался full scan.
Вопрос: хинт /*+ INDEX FULL SCAN(MIN/MAX)*/

Доброго времени суток!

есть запрос
select max(time) from table.
Поле time - типа date. Проиндексировано. Но если дать такой запрос? план будет:
INDEX FULL SCAN. и действительно выбирает долго....
может есть хинт типа INDEX FULL SCAN(MIN/MAX) или INDEX FAST FULL SCAN(MIN/MAX)...

заранее благодарю!
Ответ:
-2-
dimyaz
Есть какие-то проблемы с реверсами?
Как ты себе представляешь сравнение на больше-меньше реверсированного значения?


Никак, спасибо
Вопрос: Index Scan vs Seq Scan

Работаю с одним запросом (хотя дело не в нем ситуация, достаточно типична):

SELECT MAX(t2.key0) AS e0
      FROM Sale_userInvoice t1
      JOIN PriceList_priceListLedgerLedgerPriceListTypeStock t0 ON t0.key2=t1.Sale_supplierStockUserInvoice_UserInvoice
      AND t0.key1=457
      JOIN PriceList_priceListLedger t2 ON t2.key0=t0.key0
      WHERE t1.key0=40152341


При его выполнении получаю следующий план:

"Aggregate  (cost=89223.56..89223.57 rows=1 width=4) (actual time=553.135..553.135 rows=1 loops=1)"
"  Output: max(t2.key0)"
"  Buffers: shared hit=4067 read=24704, temp read=4296 written=4170"
"  ->  Hash Join  (cost=62389.54..89004.19 rows=87747 width=4) (actual time=381.596..550.220 rows=76991 loops=1)"
"        Output: t2.key0"
"        Hash Cond: (t0.key0 = t2.key0)"
"        Buffers: shared hit=4067 read=24704, temp read=4296 written=4170"
"        ->  Nested Loop  (cost=454.38..19469.77 rows=87747 width=4) (actual time=3.699..16.052 rows=76991 loops=1)"
"              Output: t0.key0"
"              Buffers: shared hit=2445"
"              ->  Index Scan using pk_sale_userinvoice on public.sale_userinvoice t1  (cost=0.28..2.29 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)"
"                    Output: t1.key0, t1.sale_operationuserinvoice_userinvoice, t1.salepurchaseinvoice_createpurchaseinvoiceuserinvoice_userinvoic, t1.sale_isposteduserinvoice_userinvoice, t1.salepurchaseinvoiceshipment_createpurchaseshipmentuserinvoice_u,  (...)"
"                    Index Cond: (t1.key0 = 40152341)"
"                    Buffers: shared hit=3"
"              ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t0  (cost=454.10..19118.53 rows=34895 width=8) (actual time=3.691..11.292 rows=76991 loops=1)"
"                    Output: t0.key0, t0.key1, t0.key2, t0.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu, t0.pricelist_fromdatetimepricelistledgerledgerpricelisttypestock_n, t0.pricelist_todatetimepricelistledgerledgerpricelisttypestock_nu (...)"
"                    Recheck Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                    Buffers: shared hit=2442"
"                    ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..445.38 rows=34895 width=0) (actual time=3.486..3.486 rows=76991 loops=1)"
"                          Index Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                          Buffers: shared hit=213"
"        ->  Hash  (cost=39810.96..39810.96 rows=1348496 width=4) (actual time=362.824..362.824 rows=1348496 loops=1)"
"              Output: t2.key0"
"              Buckets: 4096  Batches: 64  Memory Usage: 753kB"
"              Buffers: shared hit=1622 read=24704, temp written=3855"
"              ->  Seq Scan on public.pricelist_pricelistledger t2  (cost=0.00..39810.96 rows=1348496 width=4) (actual time=0.060..205.528 rows=1348496 loops=1)"
"                    Output: t2.key0"
"                    Buffers: shared hit=1622 read=24704"
"Total runtime: 553.626 ms"


Особенность его в том, что Postgres решает бежать по всему priceListLedger а там 1,3М записей.

Выключаю hash_join и merge_join:

План становится следующим:

"Aggregate  (cost=177127.26..177127.27 rows=1 width=4) (actual time=156.679..156.679 rows=1 loops=1)"
"  Output: max(t2.key0)"
"  Buffers: shared hit=306508 read=4313"
"  ->  Nested Loop  (cost=454.81..176907.89 rows=87747 width=4) (actual time=6.319..152.687 rows=76991 loops=1)"
"        Output: t2.key0"
"        Buffers: shared hit=306508 read=4313"
"        ->  Nested Loop  (cost=454.38..19469.77 rows=87747 width=4) (actual time=6.102..34.438 rows=76991 loops=1)"
"              Output: t0.key0"
"              Buffers: shared read=2445"
"              ->  Index Scan using pk_sale_userinvoice on public.sale_userinvoice t1  (cost=0.28..2.29 rows=1 width=4) (actual time=0.028..0.030 rows=1 loops=1)"
"                    Output: t1.key0, t1.sale_operationuserinvoice_userinvoice, t1.salepurchaseinvoice_createpurchaseinvoiceuserinvoice_userinvoic, t1.sale_isposteduserinvoice_userinvoice, t1.salepurchaseinvoiceshipment_createpurchaseshipmentuserinvoice_u,  (...)"
"                    Index Cond: (t1.key0 = 40152341)"
"                    Buffers: shared read=3"
"              ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t0  (cost=454.10..19118.53 rows=34895 width=8) (actual time=6.067..30.114 rows=76991 loops=1)"
"                    Output: t0.key0, t0.key1, t0.key2, t0.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu, t0.pricelist_fromdatetimepricelistledgerledgerpricelisttypestock_n, t0.pricelist_todatetimepricelistledgerledgerpricelisttypestock_nu (...)"
"                    Recheck Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                    Buffers: shared read=2442"
"                    ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..445.38 rows=34895 width=0) (actual time=5.786..5.786 rows=76991 loops=1)"
"                          Index Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                          Buffers: shared read=213"
"        ->  Index Only Scan using pk_pricelist_pricelistledger on public.pricelist_pricelistledger t2  (cost=0.43..1.78 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=76991)"
"              Output: t2.key0"
"              Index Cond: (t2.key0 = t0.key0)"
"              Heap Fetches: 76991"
"              Buffers: shared hit=306508 read=1868"
"Total runtime: 156.747 ms"


В 4 раза быстрее, и это на холодную базу (повторный запрос еще быстрее 123 сек). Особенность этого плана в том, что Postgres уже бежит по индексу, и делает это гораздо быстрее.

Что интересно cost'ы у них приблизительно равны, но у варианта с index'ом разброс больше 454.81..176907.89 против 62389.54..89004.19 у hash join, при этом верхний Aggregate берет пессимистичный вариант (наверное единственное место во всей СУБД, где постгрес идет по пессимистичному сценарию), и получается, что вариант с индексом СУБД кажется хуже. Конечно ее в чем то можно понять, она не знает что у меня очень быстрый SSD и дофига RAM'а под shared_buffer'ы, но тут вопрос как заставить СУБД в таком случае использовать индексы чаще scan'ов. По идее должен помогать random_page_cost, но я его и так в 1.0 поставил - меньше ставить не хочется, других настроек planner'а я не нашел :(
Ответ:
Nitro_Junkie
Что интересно cost'ы у них приблизительно равны, но у варианта с index'ом разброс больше 454.81..176907.89 против 62389.54..89004.19 у hash join, при этом верхний Aggregate берет пессимистичный вариант (наверное единственное место во всей СУБД, где постгрес идет по пессимистичному сценарию)


454.81..176907.89

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

fsync выключать особого профита нет, если только нет желания из бэкапа при сбое восстанавливаться. тоже самое можно получить и с synchronous_commit=off, wal_writer_delay побольше.
Вопрос: Oracle выбирает один план выполнения с индексом. Но другой индекс лучше

Проверяю запрос запрос к одной таблице ~ 10 000 000 записей.
Есть несколько индексов.
Сам Oracle выбирает один из них IN_TEST_3.
Если же я указываю другой индекс IN_PRODUCT_CV_TY_ST_CRDT, статистика и по времени выполнения и по чтениям изрядно лучше.

Почему?

Статистика по индекса и таблице собрана.




select /*+ gather_plan_statistics */
...
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.08 | 838 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.08 | 838 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TA_PRODUCT | 1 | 563 | 0 |00:00:00.08 | 838 |
|* 3 | INDEX RANGE SCAN | IN_TEST_3 | 1 | 810 | 106 |00:00:00.01 | 752 |
-----------------------------------------------------------------------------------------------------
34 rows selected.


select /*+ gather_plan_statistics index (p IN_PRODUCT_CV_TY_ST_CRDT) */
...
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 10 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 10 |
| 2 | INLIST ITERATOR | | 1 | | 0 |00:00:00.01 | 10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TA_PRODUCT | 4 | 563 | 0 |00:00:00.01 | 10 |
|* 4 | INDEX RANGE SCAN | IN_PRODUCT_CV_TY_ST_CRDT | 4 | 2307 | 0 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
34 rows selected.
Ответ: Сбило с толку видимо

xtender
очевидно же... оракл считает, что второй индекс менее селективен. Проверяйте условия и сравнивайте со статистиками
expimp
select /*+ gather_plan_statistics */
...
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.08 | 838 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.08 | 838 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TA_PRODUCT | 1 | 563 | 0 |00:00:00.08 | 838 |
|* 3 | INDEX RANGE SCAN | IN_TEST_3 | 1 | >>>810<<< | >>>106<<< |00:00:00.01 | 752 |
-----------------------------------------------------------------------------------------------------
34 rows selected.


select /*+ gather_plan_statistics index (p IN_PRODUCT_CV_TY_ST_CRDT) */
...
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 10 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 10 |
| 2 | INLIST ITERATOR | | 1 | | 0 |00:00:00.01 | 10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TA_PRODUCT | 4 | 563 | 0 |00:00:00.01 | 10 |
|* 4 | INDEX RANGE SCAN | IN_PRODUCT_CV_TY_ST_CRDT | 4 | >>>2307<<< | 0 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
34 rows selected.
Вопрос: Выборка уникальных значений из хронологической таблицы

Иногда требуется сделать джойн по хитрому условию: для каждой записи ведущей таблицы выбрать только одну запись ведомой таблицы (обычно максимальную или минимальную) из всех, удовлетворяющих условиям соединения.

Как правило, это нужно для хронологических таблиц типа курсов валют: например, для каждой проводки необходимо выбрать эффективный курс на дату этой проводки.

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

Обычные джойны с группировкой и сортировкой работают медленно, и тут нам на помощь приходит хитрость. Блюстители чистоты SQL меня, конечно, засмеют, ну да мне не привыкать :)

Итак, дано:

Таблица проводок:
CREATE TABLE transactions (xid NUMBER, xsum FLOAT, xdate DATE, xcurrency NUMBER)

Таблица курсов:
CREATE TABLE rates (rcurrency NUMBER, rdate DATE, rrate FLOAT)

Итак, для начала создадим уникальный индекс:

CREATE UNIQUE INDEX ux_rate_currency_date ON rates (rcurrency, rdate)

Как правило, этот индекс и должен быть уникальным: два курса одной валюты на одну дату — это плохо.

Пишем запрос:

SELECT	(
	SELECT	/*+ INDEX_DESC (r ux_rate_currency_date) */
		rrate
	FROM	rates r
	WHERE	r.rcurrency = x.xcurrency
		AND r.rdate <= x.xdate
		AND rownum = 1
	) AS eff_rate
FROM	transactions x

Если мы посмотрим на план, то увидим там

ACCESS PATH: INDEX RANGE SCAN DESCENDING

, а также условие:

COUNT STOPKEY

Что это значит?

Это значит, что для каждой строки из ведущей таблицы (таблицы проводок) Oracle будет находить первую удовлетворяющую его запись путём сканирования по индексу, а затем останавливаться по условию rowcount = 1 и выкидывать результат в итоговый запрос в качестве поля eff_rate.

То есть для каждой строки из таблицы transactions у нас будет дополнительное поле с эффективным курсом валюты проводки на её, проводки, дату — что от нас и требовалось.

Если не будет найдено ни одной записи, то eff_rate примет значение NULL.

Такая схема очень хорошо и быстро работает, но имеет один довольно существенный недостаток: при изменении названия индекса или его удалении запрос будет продолжать работать, но выдавать неверные значения.

К сожалению, Oracle версии 9i не позволяет передавать в FIELD SUBQUERY поля из внешнего запроса, если глубина вложения FIELD SUBQUERY больше 1. То есть такой, казалось бы, естественный запрос:

SELECT	(
	SELECT	rrate
	FROM	(
		SELECT	rate
		FROM	rates r
		WHERE	r.rcurrency = x.xcurrency
			AND r.rdate <= x.xdate
		ORDER BY
			r.rdate DESC
		)
	WHERE	rownum = 1
FROM	transactions x

приведёт к ошибке ORA-00904: недопустимый идентификатор.

Поэтому и приходится использовать хинты для явного прописывания логики запроса.
Ответ:
Квасной
Question: if I do specify full set of hints (with a STORED OUTLINE, for instance, or with hints as such), all tables and indexes do exist and are is usable state, then do I ensure the execution plan?


Well, problem is it is difficult (at least for me) to determine what constitutes a full set of hints for a specific SQL statement. In terms of STORED OUTLINES, CBO should use same plan, however, in general, it might require certain INIT.ORA parameters be set same way (for example, QUERY_REWRITE_ENABLED, STAR_TRANSFORMATION_ENABLED, OPTIMIZER_FEATURES_ENABLE).

Квасной
Question: if CBO does use INDEX RANGE SCAN DESCENDING and returns rows not in descending order of indexed values (as said in specification), will it be considered a bug?


No. For a simple reason you heard "several hundred times before". Besides, if you are joining tables, CBO might use index in desc order but then use hash join or some sort of conversion to bitmap which will affect resultset row order or simply whoever wrote CBO code felt like it. All INDEX_DESC hint tells CBO is how to access the table - it does not tell it to return it in desc or any other, for that matter, order.

SY.
Вопрос: Filter cost в Index Scan

Есть большая таблица ( >3млн записей). Там выполняется примитивный запрос:

SELECT t1.key0 AS jkey0
FROM ReceiptSkuLedger_ReceiptSaleSkuLedger t1
JOIN
  t_65 t0 ON t0.k0=t1.ReceiptSkuLedger_sku_ReceiptSaleSkuLedger
AND t0.k1=t1.ReceiptSkuLedger_date_ReceiptSaleSkuLedger
AND t0.k2=t1.ReceiptSkuLedger_price_ReceiptSaleSkuLedger
AND t0.k3=t1.ReceiptSkuLedger_departmentStore_ReceiptSaleSkuLedger


По сути берется временная таблица и в большой таблице отбираются все записи с 4 полями из этой временной таблицы.

В таблице есть несколько индексов. В частности по всем 4 полям, но есть по 2 и 3-м (price+date например). При выполнении postgres почему-то выбирает индекс с наименьшим числом полей и получает такой план:

"Nested Loop  (cost=0.56..279.95 rows=1 width=4) (actual time=0.050..125.175 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=142319, local hit=2"
"  ->  Seq Scan on pg_temp_2.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.003..0.024 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.83 rows=1 width=22) (actual time=0.220..0.417 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=142319"
"Planning time: 0.577 ms"
"Execution time: 125.221 ms"


Если убрать этот индекс, он начинает использовать индекс по 3, и только когда никакого другого индекса нет, начинает использовать индекс по 4 и получает следующий план:

"Nested Loop  (cost=0.56..281.45 rows=1 width=4) (actual time=0.017..1.769 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=1503, local hit=2"
"  ->  Seq Scan on pg_temp_8.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.003..0.016 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Only Scan using receiptskuledger_date_receiptsaleskuledger_receiptskuledger_dep on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.84 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=300)"
"        Output: t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.key0"
"        Index Cond: ((t1.receiptskuledger_date_receiptsaleskuledger = t_65.k1) AND (t1.receiptskuledger_departmentstore_receiptsaleskuledger = t_65.k3) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0) AND (t1.receiptskuledger_price_receiptsales (...)"
"        Heap Fetches: 300"
"        Buffers: shared hit=1503"
"Planning time: 0.487 ms"
"Execution time: 1.809 ms"


Проблема с использованием индекса усугубляется, когда shared_buffers'ов не хватает и план становится таким:

"Nested Loop  (cost=0.56..279.95 rows=1 width=4) (actual time=0.269..970.659 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=28900 read=113419 written=15, local hit=2"
"  ->  Seq Scan on pg_temp_8.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.004..0.164 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.83 rows=1 width=22) (actual time=1.649..3.233 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=28900 read=113419 written=15"
"Planning time: 0.776 ms"
"Execution time: 970.736 ms"


(то есть еще переполняется shared_buffers, что совсем не айс).

Причем плохой план она выбирает, потому что cost чуть-чуть меньше. По идее на него должен влиять effective_cache_size, но он ни на что не влияет (если его поставить даже 20МБ план остается)

Также при изменении page_cost, tuple_cost, cost'ы увеличиваются:

"Nested Loop  (cost=0.56..2585.75 rows=1 width=4) (actual time=0.072..126.142 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=142319, local hit=2"
"  ->  Seq Scan on pg_temp_2.t_65  (cost=0.00..5.00 rows=300 width=18) (actual time=0.003..0.030 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..8.59 rows=1 width=22) (actual time=0.219..0.420 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=142319"
"Planning time: 0.601 ms"
"Execution time: 126.192 ms"


Но при этом этот план все равно приоритетнее. Причем на другом компе с такой же версией БД и теми же настройками она выбирает правильный план (но на большинстве все же не правильный).

Вопрос что влияет на определения cost'а при Filter в Index Scan. Почему он такой маленький и что на него еще может влиять?
Ответ:
Nitro_Junkie
Сейчас сделал VACUUM ANALYZE и везде план стал нормальным. Но непонятно почему. На всех базах по этой таблице делается VACUUM FULL раз в неделю, при приросте всего на 2-3% за этот период. Этого получается недостаточно?

Недостаточно. Помимо прироста бывают ещё и измения. Для большой таблицы 2% прироста легко проскакивают порог, после которого планировщик считает таблицу "слишком грязной" и игнорирует IOS.

VACUUM FULL делать часто не надо, это специальная команда которая перестраивает таблицу и индексы. Она думана для случаев, когда совсем всё плохо.
Настройте autovacuum агрессивно, я делаю так:
параметрзначение
autovacuum_analyze_scale_factor0.02
autovacuum_vacuum_scale_factor0.005
autovacuum_vacuum_cost_delay-1
autovacuum_vacuum_cost_limit-1
autovacuum_max_workers35 (не меньше 10)
autovacuum_work_mem256MB
log_autovacuum_min_duration1000
vacuum_cost_delay0
vacuum_freeze_min_age10000000
Вопрос: Как работает Clustered Index Scan?

Добрый день. Случайно наткнулся на один интересный факт, которому пока не нашел объяснения. Для получения идентичного результата шаги нижеприведенных скриптов должны выполняться последовательно.

Подготовка данных:
+
use tempdb;

set ansi_nulls on;

if object_id( 'dbo.test' ) is not null drop table dbo.test;

create table dbo.test
  ( a char(  900 ) not null
  , b char( 7000 ) not null );
Тест с кучей:
+
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- с кучей вроде все понятно... сколько страниц с данными, столько и логических чтений, причем обращение к IAM-странице вроде бы не считается...
Тест 1:
+
create clustered index clix_test on test( a );

dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 2 logical reads?
Тест 2:
+
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с индексом, 2 страницы с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 4 logical reads?
Тест 3:
+
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 корневая страница, 2 промежуточных страницы, 10 страниц с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 14 logical reads?
Тест 4:
+
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 корневая страница, 4 промежуточных страницы, 18 страниц с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 24 logical reads?
Удаление тестовой таблицы:
+
drop table dbo.test;

Мое видение:

1. Если у нас куча, то SQL Server находит IAM-страницу, по которой находит страницы с данными. В этом случае в logical reads указывается число, совпадающее с количеством страниц с данными, вроде бы все сходится.
2. Если у нас кластерный индекс, то у SQL Server есть два варианта выполнения сканирования: Index Ordered Scan и Allocation Ordered Scan ( ). В приведенных примерах скорее всего сервер выберет Index Ordered Scan. В этом случае, как я понимаю, ищется сначала корневая страница, потом по дереву находится страница с первой записью, далее скуль перебирает страницы по ссылкам в заголовках. Однако, я пока не могу объяснить то количество логических чтений которое возвращает сервер. Такое впечатление, что в logical reads указывается количество всех страниц, занятых таблицей - страницы с данными, все страницы индекса, IAM страница, хотя на текущий момент абсолютно не понятно, зачем серверу понадобилось читать все страницы индекса.

Уважаемое сообщество, подскажите, пожалуйста, где в своих рассуждениях я могу ошибаться?

Версия SQL:
+
Microsoft SQL Server 2014 - 12.0.2269.0 (X64) 
Jun 10 2015 03:35:45 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )
Ответ: o-o, спасибо за разъяснения! Нюансов много, но в целом картина стала гораздо понятнее.
Вопрос: Проходит полный Seq Scan вместо индекса.

Приветствую,

Не пойму почему используется Seq Scan вместо индекса, обходит 500М записей ради ~1300.

 Nested Loop  (cost=3223965.89..20135328.45 rows=2539646 width=115) (actual time=94589.422..131043.163 rows=1381 loops=1)
   Output: i0.id, i0.transaction_id, i0."position", i0.is_coinbase, i0.vout, t2.trx_id, o1.addresses, o1.amount
   ->  Hash Join  (cost=3223965.32..17251459.53 rows=2539646 width=54) (actual time=94589.376..131032.096 rows=1381 loops=1)
         Output: i0.id, i0.transaction_id, i0."position", i0.is_coinbase, i0.vout, o1.addresses, o1.amount, o1.transaction_id
         Hash Cond: (o1.id = i0.output_id)
         ->  Seq Scan on public.outputs o1  (cost=0.00..10832460.94 rows=507146494 width=41) (actual time=0.014..48529.711 rows=505393641 loops=1)
               Output: o1.id, o1.transaction_id, o1.addresses, o1."position", o1.amount, o1.type, o1.req_sigs, o1.spent
         ->  Hash  (cost=3192219.74..3192219.74 rows=2539646 width=21) (actual time=10.803..10.803 rows=1381 loops=1)
               Output: i0.id, i0.transaction_id, i0."position", i0.is_coinbase, i0.vout, i0.output_id
               Buckets: 4194304  Batches: 1  Memory Usage: 32844kB
               ->  Index Scan using inputs_transaction_id_position_index on public.inputs i0  (cost=0.57..3192219.74 rows=2539646 width=21) (actual time=0.037..10.093 rows=2731 loops=1)
                     Output: i0.id, i0.transaction_id, i0."position", i0.is_coinbase, i0.vout, i0.output_id
                     Index Cond: (i0.transaction_id = ANY ('{165237854,.......}'::integer[]))
   ->  Index Scan using transactions_pkey on public.transactions t2  (cost=0.57..1.13 rows=1 width=69) (actual time=0.007..0.007 rows=1 loops=1381)
         Output: t2.id, t2.block_id, t2.trx_id, t2.version, t2.index, t2.lock_time, t2.size, t2."time", t2.inputs_count, t2.outputs_count, t2.mem_pool, t2.total_in_value, t2.total_out_value, t2.est_out_value, t2.fee, t2.created_at
         Index Cond: (t2.id = o1.transaction_id)
 Planning time: 7.658 ms
 Execution time: 131045.677 ms
(18 rows)


Запрос
explain (analyze, verbose) 
SELECT i0."id", i0."transaction_id", i0."position", i0."is_coinbase", i0."vout", t2."trx_id", o1."addresses", o1."amount" 
FROM "inputs" AS i0 
INNER JOIN "outputs" AS o1 ON o1."id" = i0."output_id" 
INNER JOIN "transactions" AS t2 ON t2."id" = o1."transaction_id" 
WHERE (i0."transaction_id" = ANY(ARRAY[165237854,.......]));


Естественно есть primary индекс на outputs.id:
                                                  Table "public.outputs"
     Column     |   Type    |                      Modifiers                       | Storage  | Stats target | Description
----------------+-----------+------------------------------------------------------+----------+--------------+-------------
 id             | integer   | not null default nextval('outputs_id_seq'::regclass) | plain    |              |
 transaction_id | integer   |                                                      | plain    |              |
 addresses      | integer[] | default ARRAY[]::integer[]                           | extended |              |
 position       | integer   |                                                      | plain    |              |
 amount         | bigint    |                                                      | plain    |              |
 type           | integer   |                                                      | plain    |              |
 req_sigs       | integer   |                                                      | plain    |              |
 spent          | boolean   | default false                                        | plain    |              |
Indexes:
    "outputs_pkey" PRIMARY KEY, btree (id)
    "outputs_addresses_index" btree (addresses)
    "outputs_transaction_id_position_index" btree (transaction_id, "position")
Foreign-key constraints:
    "outputs_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transactions(id)
Referenced by:
    TABLE "inputs" CONSTRAINT "inputs_output_id_fkey" FOREIGN KEY (output_id) REFERENCES outputs(id)
    TABLE "output_datas" CONSTRAINT "output_datas_output_id_fkey" FOREIGN KEY (output_id) REFERENCES outputs(id)
Ответ: nateless,

а если так?

set enable_nestloop = off;
SELECT i0."id", i0."transaction_id", i0."position", i0."is_coinbase", i0."vout", t2."trx_id", o1."addresses", o1."amount"
FROM "inputs" AS i0

INNER JOIN (SELECT ...) AS a ON a.? = i0."transaction_id"

INNER JOIN "outputs" AS o1 ON o1."id" = i0."output_id"
INNER JOIN "transactions" AS t2 ON t2."id" = o1."transaction_id";
Вопрос: Скорость чтения при Index Scan и Seq Scan

Добрый день, уважаемые специалисты!

Прошу помочь разобраться с двумя вопросами. Значит имеется Postgresql 9.6 CPU I7 16Гб ОЗУ обычный диск 1 Тб
Тестовая база создана утилитой pgbench с параметром -s равным 2000
В тестовых целях выполняется два запроса.

Запрос первый:
explain (analyze, buffers, timing)
select aid, count(*) from public.pgbench_accounts
group by aid

План запроса ниже
+

GroupAggregate (cost=0.57..8193532.57 rows=200000000 width=12) (actual time=23.969..63151.347 rows=200000000 loops=1)
Group Key: aid
Buffers: shared hit=101 read=546452
I/O Timings: read=10276.165
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..5193532.57 rows=200000000 width=4) (actual time=23.962..23192.594 rows=200000000 loops=1)
Heap Fetches: 0
Buffers: shared hit=101 read=546452
I/O Timings: read=10276.165
Planning time: 10.919 ms
Execution time: 68511.093 ms


Скорость чтения по IOTOP составила примерно 71-72МБ/с. Хотя если взять (546452 * 8) / 10 / 1024 получиться 426 МБ/с

Второй запрос:
explain (analyze, buffers, timing)
select abalance, count(*) from public.pgbench_accounts
group by abalance

План запроса:
+

HashAggregate (cost=6278689.00..6278689.01 rows=1 width=12) (actual time=152545.057..152545.057 rows=1 loops=1)
Group Key: abalance
Buffers: shared read=3278689
I/O Timings: read=111095.229
-> Seq Scan on pgbench_accounts (cost=0.00..5278689.00 rows=200000000 width=4) (actual time=34.025..122496.127 rows=200000000 loops=1)
Buffers: shared read=3278689
I/O Timings: read=111095.229
Planning time: 30.275 ms
Execution time: 152545.370 ms


Скорость чтения по IOTOP составила примерно 184 МБ/с. Хотя если взять (3278689 * 8) / 111 / 1024 получиться 230 МБ/с
Скриншоты с данными IOTOP могу приложить.

Скажите пожалуйста:
1. Почему по IOTOP скорость чтения индекса сильно меньше скорости чтения таблицы?
2. Почему скорости подсчитанные по данным explain превышают физическую скорость которую может дать диск?
Ответ: Alexius,

Перед каждым запуском производился reboot, что бы исключить влияния кэша ОС