Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по многоколоночному индексу нескольких комбинаций / 8 сообщений из 8, страница 1 из 1
05.05.2017, 16:49
    #39449554
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
rollback;

begin;

create table t1(
    f1 integer,
    f2 integer,
    f3 integer,
    f4 integer)
;

insert into t1 select 1000000*random(), 100*random(), 100*random(), 100*random() from generate_series(1, 3000000);
create index on t1(f1);
create index on t1(f2, f3, f4);
analyze t1;

set enable_bitmapscan to off;

explain (analyze, buffers) select * from t1 where f1 = 1;
explain (analyze, buffers) select * from t1 where f1 in (1,2);
explain (analyze, buffers) select * from t1 where f2 in (1,2) and f3 in (1,3) and f4 in (1,4);
explain (analyze, buffers) select * from t1 where (f2,f3,f4) in ((1,1,1), (1,3,4));
explain (analyze, buffers) select * from t1 where (f2,f3,f4) in ((1,1,1), (2,3,4));

-- commit;


 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using t1_f1_idx on t1  (cost=0.43..20.50 rows=4 width=16) (actual time=0.028..0.032 rows=3 loops=1)
   Index Cond: (f1 = 1)
   Buffers: shared hit=3 read=3
 Planning time: 0.258 ms
 Execution time: 0.061 ms
(5 строк)

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using t1_f1_idx on t1  (cost=0.43..36.99 rows=7 width=16) (actual time=0.005..0.014 rows=6 loops=1)
   Index Cond: (f1 = ANY ('{1,2}'::integer[]))
   Buffers: shared hit=12
 Planning time: 0.067 ms
 Execution time: 0.049 ms
(5 строк)

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_f2_f3_f4_idx on t1  (cost=0.43..131.98 rows=24 width=16) (actual time=0.039..0.110 rows=25 loops=1)
   Index Cond: ((f2 = ANY ('{1,2}'::integer[])) AND (f3 = ANY ('{1,3}'::integer[])) AND (f4 = ANY ('{1,4}'::integer[])))
   Buffers: shared hit=43 read=6
 Planning time: 0.093 ms
 Execution time: 0.133 ms
(5 строк)

                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_f2_f3_f4_idx on t1  (cost=0.43..65379.35 rows=6 width=16) (actual time=0.243..39.960 rows=3 loops=1)
   Index Cond: (f2 = 1)
   Filter: (((f3 = 1) AND (f4 = 1)) OR ((f3 = 3) AND (f4 = 4)))
   Rows Removed by Filter: 30173
   Buffers: shared hit=30177 read=114 written=1
 Planning time: 0.105 ms
 Execution time: 39.984 ms
(7 строк)

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..91217.00 rows=6 width=16) (actual time=164.243..724.826 rows=4 loops=1)
   Filter: (((f2 = 1) AND (f3 = 1) AND (f4 = 1)) OR ((f2 = 2) AND (f3 = 3) AND (f4 = 4)))
   Rows Removed by Filter: 2999996
   Buffers: shared hit=16217
 Planning time: 0.118 ms
 Execution time: 724.857 ms
(6 строк)

Жаль, что в последних двух запросах постгрес не использует условие при поиске по индексу в Index Cond. При том, что с третьим запросом справляется прекрасно!

Как можно ускорить последние два запроса, чтобы условие проверялось в Index Cond? Придумал только вариант переформулировать на union all.

PS: В этом тесте положение исправляет включенный bitmapscan. В таком случае постгрес использует BitmapOr. Но в моем реальном запросе замена IndexScan на BitmapIndexScan приводит к выбору худшего плана на последующих этапах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.05.2017, 17:29
    #39449578
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
LeXa NalBat,
или расписать поэлементный AND или сделать индекс по row() (создать тип) , или попользоваться тем, что пж сам умеет разбирать поэлементно в таком синтаксе :

Код: sql
1.
explain (analyze, buffers) select * from t1 where (f2,f3,f4) in (SELECT 1,1,1 UNION ALL SELECT 2,3,4);



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Nested Loop  (cost=0.48..33.12 rows=23 width=16) (actual time=0.049..0.049 rows=0 loops=1)
  Buffers: shared hit=4 read=2
  ->  Unique  (cost=0.05..0.07 rows=2 width=12) (actual time=0.014..0.016 rows=2 loops=1)
        ->  Sort  (cost=0.05..0.06 rows=2 width=12) (actual time=0.013..0.014 rows=2 loops=1)
              Sort Key: (1), (1), (1)
              Sort Method: quicksort  Memory: 25kB
              ->  Append  (cost=0.00..0.04 rows=2 width=12) (actual time=0.002..0.002 rows=2 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1)
  ->  Index Scan using t1_f2_f3_f4_idx on t1  (cost=0.43..16.50 rows=3 width=16) (actual time=0.014..0.014 rows=0 loops=2)
        Index Cond: ((f2 = (1)) AND (f3 = (1)) AND (f4 = (1)))
        Buffers: shared hit=4 read=2
Planning time: 0.558 ms
Execution time: 0.089 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.05.2017, 17:37
    #39449586
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
ещё вариант
Код: sql
1.
explain (analyze, buffers) select * from t1 where (f2,f3,f4) in (values( 1,1,1),(2,3,4));



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Nested Loop  (cost=0.46..33.11 rows=23 width=16) (actual time=0.059..0.059 rows=0 loops=1)
  Buffers: shared hit=6
  ->  Unique  (cost=0.04..0.06 rows=2 width=12) (actual time=0.019..0.021 rows=2 loops=1)
        ->  Sort  (cost=0.04..0.04 rows=2 width=12) (actual time=0.018..0.018 rows=2 loops=1)
              Sort Key: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3
              Sort Method: quicksort  Memory: 25kB
              ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=12) (actual time=0.003..0.004 rows=2 loops=1)
  ->  Index Scan using t1_f2_f3_f4_idx on t1  (cost=0.43..16.50 rows=3 width=16) (actual time=0.015..0.015 rows=0 loops=2)
        Index Cond: ((f2 = "*VALUES*".column1) AND (f3 = "*VALUES*".column2) AND (f4 = "*VALUES*".column3))
        Buffers: shared hit=6
Planning time: 0.299 ms
Execution time: 0.121 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.05.2017, 17:57
    #39449601
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
qwwqещё вариант
Код: sql
1.
explain (analyze, buffers) select * from t1 where (f2,f3,f4) in (values( 1,1,1),(2,3,4));



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Nested Loop  (cost=0.46..33.11 rows=23 width=16) (actual time=0.059..0.059 rows=0 loops=1)
  Buffers: shared hit=6
  ->  Unique  (cost=0.04..0.06 rows=2 width=12) (actual time=0.019..0.021 rows=2 loops=1)
        ->  Sort  (cost=0.04..0.04 rows=2 width=12) (actual time=0.018..0.018 rows=2 loops=1)
              Sort Key: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3
              Sort Method: quicksort  Memory: 25kB
              ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=12) (actual time=0.003..0.004 rows=2 loops=1)
  ->  Index Scan using t1_f2_f3_f4_idx on t1  (cost=0.43..16.50 rows=3 width=16) (actual time=0.015..0.015 rows=0 loops=2)
        Index Cond: ((f2 = "*VALUES*".column1) AND (f3 = "*VALUES*".column2) AND (f4 = "*VALUES*".column3))
        Buffers: shared hit=6
Planning time: 0.299 ms
Execution time: 0.121 ms



*RESPECT*
решение красивое и "идеологически выдержанное"
почему то большая часть разработчиков про конструкцию values () не догадывается к сожалению.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.05.2017, 19:08
    #39449619
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
Maxim Boguk,

думаю ТС-а это не спасёт ,
т.к. примитивный вариант:
Код: sql
1.
2.
3.
explain (analyze, buffers) 
     select * from t1 where (f2,f3,f4) =( 1,1,1) 
     UNION ALL select * from t1 where (f2,f3,f4) =(2,3,4);


он не поюзал. т.е. он с какими то своими тараканами сражается
а не с планировщиком
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.05.2017, 22:03
    #39449813
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
кажется понял:
тс хотел бы ,вероятно, уйти от какого--то BitmapAnd (долгого построения карты по 2-м индексам [могу врать]), но т.к. енейбл общий -- чисто технический enable_bitmapscan, то 2 логически разных действия (2индексный поиск[BitmapAnd] и выбор из списка BitmapOR) регулируются одним и тем же крыжиком.
Т.е. крыжиков в пж мало для такого тюнинга.

с другой, для листа (или перечисления ключей в OR) не предусмотрено использования планером nestedLoop-а, и при дизейбле bitmapscan планер сразу проваливается в секскан для списка. (пока строк не станет слишком много). а подсунуть ему сет вместо листа можно только переписав запрос. (тут оно и "опускается" до нетстед лупа)


могу врать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.05.2017, 22:05
    #39449815
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
ср:
Код: sql
1.
explain (analyze, buffers) select * from t1 where f1 =1 or f1=2;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.05.2017, 09:25
    #39451811
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
поиск по многоколоночному индексу нескольких комбинаций
qwwq,

спаибо! Вариант с values очень интересный, элегантнее union all, обязательно его попробую в боевом запросе.

qwwqпримитивный вариант ... UNION ALL он не поюзалУже поюзал, работает. Буду пробовать values.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по многоколоночному индексу нескольких комбинаций / 8 сообщений из 8, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 159ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1996524]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 37ms
total:  159ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]