ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / поиск по многоколоночному индексу нескольких комбинаций
8 сообщений из 8, страница 1 из 1
поиск по многоколоночному индексу нескольких комбинаций
    #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, 16:49
| Ответить | Цитировать | Написать  
поиск по многоколоночному индексу нескольких комбинаций
    #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:29
| Ответить | Цитировать | Написать  
поиск по многоколоночному индексу нескольких комбинаций
    #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:37
| Ответить | Цитировать | Написать  
поиск по многоколоночному индексу нескольких комбинаций
    #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, 17:57
| Ответить | Цитировать | Написать  
поиск по многоколоночному индексу нескольких комбинаций
    #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
05.05.2017, 19:08
| Ответить | Цитировать | Написать  
поиск по многоколоночному индексу нескольких комбинаций
    #39449813
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
кажется понял:
тс хотел бы ,вероятно, уйти от какого--то BitmapAnd (долгого построения карты по 2-м индексам [могу врать]), но т.к. енейбл общий -- чисто технический enable_bitmapscan, то 2 логически разных действия (2индексный поиск[BitmapAnd] и выбор из списка BitmapOR) регулируются одним и тем же крыжиком.
Т.е. крыжиков в пж мало для такого тюнинга.

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


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

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

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


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