ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
25 сообщений из 34, страница 1 из 2
  1  все
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260523
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Простой тест.
Вот у вас есть страница которая принимает на вход id чего либо (цело число)
и идет в таблицу за этим id, и вы даже проверили что у вас на входе число а не черте что.

Далее test case:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
postgres=# create table test as select id from generate_series(1, 100000) as g(id);
SELECT 100000
postgres=# alter table test add primary key (id);
ALTER TABLE
postgres=# analyze test;
ANALYZE
postgres=# explain analyze select * from test where id=100;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.29..8.31 rows=1 width=4) (actual time=0.050..0.052 rows=1 loops=1)
   Index Cond: (id = 100)
   Heap Fetches: 1
 Planning time: 0.349 ms
 Execution time: 0.102 ms



пока все ок... а что будет если на вход реально длинное число задать:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
postgres=# explain analyze select * from test where id=100000000000000000000;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1893.00 rows=500 width=4) (actual time=18.581..18.581 rows=0 loops=1)
   Filter: ((id)::numeric = 100000000000000000000::numeric)
   Rows Removed by Filter: 100000
 Planning time: 0.159 ms
 Execution time: 18.613 ms



Оооps, а получили то мы upcast к numeric и как итог полный seq scan по таблице, а теперь представим себе что у вас в таблице не 100.000 строк а 100M и размер под 10-100Gb, и что будет с дисками и с процессором у вас если кто то десяток таких страниц откроет.
И что грустно мне заявили что working as designed.

PS: использование placeholders при отключении server side prepared statement - не спасает.

--
Проект с базой но без DBA все равно что автопарк без штатного автомеханика. Ездит пока все не сломается.

http://www.postgresql-consulting.ru/services/
...
Рейтинг: 0 / 0
22.06.2016, 16:06
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260559
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim Boguk,

ай как --то долпайопу гурию smagen пытался пояснить разницу между операндами сравнения со стороны множества и со стороны единичного значения, и почему пж должен , если бы был написан вменяемыми индусами, а не дятлами гуриями, делать примерно так:

Код: sql
1.
2.
3.
WHERE 
{set_val}::{set_type}={singl_val}::{set_type} --indexed and/or not set--calculated
 AND {set_val}::{singl_type}={singl_val}::{singl_type} -- filter if first row == true



но дятлов гуриев же не переубедить.

так что ешьте, что дают.
...
Рейтинг: 0 / 0
22.06.2016, 16:39
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260605
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Maxim BogukИ что грустно мне заявили что working as designed.Логично.
Не полагайся на автоматическое приведение типов, приводи передаваемый параметр к типу поля и получай ошибку, как и в случае передачи любой несовместимой с типом лабуды. Или через свой функцию приведения, которая вернет null::integer.
...
Рейтинг: 0 / 0
22.06.2016, 17:20
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260808
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
p2.Maxim BogukИ что грустно мне заявили что working as designed.Логично.
Не полагайся на автоматическое приведение типов, приводи передаваемый параметр к типу поля и получай ошибку, как и в случае передачи любой несовместимой с типом лабуды. Или через свой функцию приведения, которая вернет null::integer.

Это вы авторам 100% известных мне ORM расскажите :).

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
22.06.2016, 22:20
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260828
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim BogukПроект с базой но без DBA все равно что автопарк без штатного автомеханика. Ездит пока все не сломается.
Приходить в такой проект, когда оно уже "готово" и скоро сломается… ощущение неизбежности и бессилия :(
...
Рейтинг: 0 / 0
22.06.2016, 22:53
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260856
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

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

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

т.е. таких вещей стесняться надо, а не пяткой себя в хрудь колотить
...
Рейтинг: 0 / 0
23.06.2016, 00:15
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260866
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
qwwqять, ёптемайзеру теоретически достаточно инфы, чтобы быстро вернуть пустоту, ничего не делая. можно попробовать его принудить известными ему средствами - повесить check на min/max int. Вот только сумеет ли он этот check протолкнуть через каст к numeric.
...
Рейтинг: 0 / 0
23.06.2016, 00:39
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260879
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.,

чек даст только границу.

сделайте
Код: sql
1.
select * from test where id=1.0;


-- и получите тот же тупняк, но уже "в границах целого".

тут надо в консерватории править. но внутре -- одни консервавторы -- править некому.
и задачу осознать и принять к исполнению -- некому
вот и плодятся гурии по заворачиванию рыбы на голом месте

в случае ларри -- берём индуса , накручиваем ему хвост, и он как миленький пишет разборы всех частных случаев сам, а не конструирует детский конструктор типов кастов и сравнений. херовый, зато универсальный.
...
Рейтинг: 0 / 0
23.06.2016, 11:33
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260888
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
p2.можно попробовать его принудить известными ему средствами - повесить check на min/max int. Вот только сумеет ли он этот check протолкнуть через каст к numeric.

если немного извратиться, то да, даже работает:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create table test_s (id serial primary key);
insert into test_s select id from generate_series(1, 100000) as gs(id);

set constraint_exclusion = on;
alter table test_s add constraint test_bigint2 check (id::numeric > -9223372036854775808 and id::numeric < 9223372036854775807);

explain select * from test_s where id = 10000000000000000000;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false



где-то в критичных местах прикрыть так можно.
...
Рейтинг: 0 / 0
23.06.2016, 11:41
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39260900
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexius,

точнее должно быть <= и >= в check для bigint.
...
Рейтинг: 0 / 0
23.06.2016, 11:55
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39261898
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
qwwqp2.,

чек даст только границу.

сделайте
Код: sql
1.
select * from test where id=1.0;


-- и получите тот же тупняк, но уже "в границах целого".

тут надо в консерватории править. но внутре -- одни консервавторы -- править некому.
и задачу осознать и принять к исполнению -- некому
вот и плодятся гурии по заворачиванию рыбы на голом месте

в случае ларри -- берём индуса , накручиваем ему хвост, и он как миленький пишет разборы всех частных случаев сам, а не конструирует детский конструктор типов кастов и сравнений. херовый, зато универсальный.

нет оракуля под рукой, но вагную будет index unique scan и table access by rowid

как-то так.
...
Рейтинг: 0 / 0
24.06.2016, 15:37
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39261970
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Jonhsonнет оракуля под рукой, но вагную будет index unique scan и table access by rowid в оракле отсутствует честный int4 и другой порядок приведения типов, но ситуация аналогична.
Если сделать ключ integer (number) и задать в запросе значение типа binary_float/double: id = 1d, получим приведение айдишника к параметру и full scan.
...
Рейтинг: 0 / 0
24.06.2016, 16:41
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39261979
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
p2.Jonhsonнет оракуля под рукой, но вагную будет index unique scan и table access by rowid в оракле отсутствует честный int4 и другой порядок приведения типов, но ситуация аналогична.
Если сделать ключ integer (number) и задать в запросе значение типа binary_float/double: id = 1d, получим приведение айдишника к параметру и full scan.

не вполне понятен ваш поток сознания, дайте П ример в студию что вы имеете ввиду
...
Рейтинг: 0 / 0
24.06.2016, 16:46
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262005
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Jonhsonдайте П римерпросто переведи пгшный синтаксис
Код: sql
1.
select * from test where id=1.0;

на оракловый
Код: sql
1.
select * from test where id=1.0d;
...
Рейтинг: 0 / 0
24.06.2016, 17:02
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262019
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
опять же оракуля под рукой нет (, но уверен, что to_number() спасёт отца русской демократии
...
Рейтинг: 0 / 0
24.06.2016, 17:08
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262060
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.,

идиотизм заразен, да Предикат TO_BINARY_DOUBLE("xxx"."xxx")=1d,
используемый в строке с идентификатором 1 плана выполнения, содержит неявное
преобразование типа данных для индексированного столбца "xxx". Это
неявное преобразование типа данных не позволяет оптимизатору выбрать индексы
для таблицы

-- что радует -- что в сракле оно не лучше.
ларри забыл накрутить хвоста индусу
или решил, что обучение свидетелей заворачиванию рыбы -- оно прибыльнее, чем сделать один раз -- на века, именно там -- где ему место.

мелочь, а приятно.
...
Рейтинг: 0 / 0
24.06.2016, 17:54
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262109
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
внезапно

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
select * from t99 where id=to_number(4.3d);

строки не выбраны

Затрач.время: 00:00:00.01

План выполнения
----------------------------------------------------------
Plan hash value: 1056661905

--------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |	 1 |	17 |	 3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T99	   |	 1 |	17 |	 3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | SYS_C0010969 |	 1 |	   |	 2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("ID"=4.2999999999999998)



где т99:

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create table t99 as select level as id, 'ttttt'|| level as val from dual connect by level<=1000000;

Таблица создана.

Затрач.время: 00:00:00.94
SH3rhbase_sh>select count(*) from t99;

  COUNT(*)
----------
   1000000

Затрач.время: 00:00:00.01
SH3rhbase_sh>alter table t99 add primary key(id);

Таблица изменена.

Затрач.время: 00:00:00.43

...
Рейтинг: 0 / 0
24.06.2016, 20:19
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262118
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Jonhson,

вот за что я вас, дятлов, нежно люблю -- так это за готовность нести околесицу, не разобравшись.

после приведения любой идиот сможет.

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

типа ударения себя пяткой в грудь и патетических восклицаний об ущербности не причастных к.
...
Рейтинг: 0 / 0
24.06.2016, 20:46
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262168
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
честно говоря не вижу проблемы в приведении типа, нет оно конечно может быть и лучше автоприведение, но вообще-то типы для того и изобрели, что-бы отличать данные.


И вот вам пример, гораздо менее надуманный и более жизненный:

Код: sql
1.
select * from t99 where id = '5';
...
Рейтинг: 0 / 0
25.06.2016, 01:10
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262210
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Jonhsonчестно говоря не вижу проблемы в приведении типа, нет оно конечно может быть и лучше автоприведение, но вообще-то типы для того и изобрели, что-бы отличать данные.


И вот вам пример, гораздо менее надуманный и более жизненный:

Код: sql
1.
select * from t99 where id = '5';



у PG кстати тут Index scan будет.
А про приведение типов - это вы расскажите авторам 100% известных мне ORM которые суют в запросы что ни попадя и про проверку или приведение типов - не думают.

PS: забавно
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
mboguk=# explain analyze select * from table1 where id=50000000000000000000;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on table1  (cost=0.00..18922.00 rows=5000 width=8) (actual time=184.126..184.126 rows=0 loops=1)
   Filter: ((id)::numeric = 50000000000000000000::numeric)
   Rows Removed by Filter: 1000000
 Planning time: 0.093 ms
 Execution time: 184.154 ms
(5 rows)

mboguk=# explain analyze select * from table1 where id='50000000000000000000';
ERROR:  value "50000000000000000000" is out of range for type integer
LINE 1: explain analyze select * from table1 where id='5000000000000...'

Все таки они чего то намудрили  с приведением типов.
Факт что база работает корректно если ей как литерал число засовывать но не работает если его засунуть как число - он забавен.



Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
25.06.2016, 10:05
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262724
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
Maxim Bogukу PG кстати тут Index scan будет.


если кажете, как сделать
Код: sql
1.
 select level lv from dual connect by level<=10000000;

без заморочек я попробую прочекать

А про приведение типов - это вы расскажите авторам 100% известных мне ORM которые суют в запросы что ни попадя и про проверку или приведение типов - не думают.


это же хорошо, нам с вами больше работы
...
Рейтинг: 0 / 0
27.06.2016, 10:00
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262741
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
generate_series, нашёл
...
Рейтинг: 0 / 0
27.06.2016, 10:18
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39262746
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
действительно, в случае варчара срабатывает, единственное, что смущает, так это почему
Код: sql
1.
index scan

, а не
Код: sql
1.
index unique scan

?
...
Рейтинг: 0 / 0
27.06.2016, 10:25
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39263084
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
qwwq
пойнт в том, что уже до приведения у оптимайзера достаточно инфы, чтобы поюзать индекс. вот только ни один индус не напрягся -- и в итоге мы, с вами, товарищи дятлы, становимся носителями сакрального знания по поводу правильных приемов заворачивания рыбы. что крайне льстит дятлам, вплоть до внезапности неожиданностей.



Ну да ....

Код: plsql
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.
create table T1
(
  lv NUMBER not null,
  constraint PK_T1 primary key (LV)
);

SQL> select * from t1 where lv=cast('164300' AS CHAR(100));

Execution Plan
----------------------------------------------------------
Plan hash value: 1289504326

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     5 |     2   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_T1 |     1 |     5 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("LV"=TO_NUMBER(CAST('164300' AS CHAR(100))))

SQL>
...
Рейтинг: 0 / 0
27.06.2016, 17:25
| Ответить | Цитировать | Написать  
как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
    #39263085
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
ora601,

человеческий план, это оракуль же
...
Рейтинг: 0 / 0
27.06.2016, 17:27
| Ответить | Цитировать | Написать  
25 сообщений из 34, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / как сломать PG index scan по int полю или почему надо проверять диапазоны входных данных
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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