я так понял нельзя сделать, чтобы distinct по индексу быстро работал, как в mysql? запрос вида select distinct field from table limit 20;

всем привет, вот что-то никак не могу написать функцию виду: create or replace function generate_range(start integer, dim integer) returns integer[] as $res$ BEGIN return (select * from generate_series(start, start+dim)); END $res$ LANGUAGE plpgsql; usage: generate_range(5, 3) => 5, 6, 7 разные варианты пробовал, гуглил, но все равно не получается, может кто-то подсказать?

я бы не использовал функцию, а комбинацию ARRAY и generate_series если функция необходима, то лучше sql: CREATE OR REPLACE FUNCTION generate_range (INT, INT) RETURNS INT[] LANGUAGE SQL IMMUTABLE AS $func$ SELECT ARRAY (SELECT generate_series ($1, $2)); $func$;

я так понял нельзя сделать, чтобы distinct по индексу быстро работал, как в mysql? запрос вида select distinct field from table limit 20;

order by field добавьте, limit без order by - ат-ат-ат

а, кстати, почему?

я так понял нельзя сделать, чтобы distinct по индексу быстро работал, как в mysql? запрос вида select distinct field from table limit 20;

select distinct field from table where field is not null limit 20;

а, кстати, почему?

почему order by ? по крайней мере хочется получать предсказуемый результат https://www.postgresql.org/docs/current/static/queries-limit.html The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

но иногда это как раз то, что нужно

примерно в тех же целях, в которых есть tablesample

ты хочешь посмотреть, что бывает в таблице

select distinct field from table where field is not null limit 20;

почему "where field is not" даст ускорение?

На моих данных это поднимает селективность и делает index only scan вместо seq scan

select distinct field from table where field is not null limit 20;

А если null нету?

Тогда мне тоже интересно, как стриггерить index only scan (и нужно ли)

а кто что юзает в качестве гуевых утилит на маке?

datagrip

postico/psequel pro пробовал, бесполезные какие-то обе, хоть и мелкие-красивые на sqlpro studio наткнулся, но еще не смотрел

datagrip на всех платформах хорош.

надо заценить датагрип

юзал через idea похожее на эту

datagrip плохо показывает INTERVAL

ну или я не смог настроить :)

Коллеги, подскатиже есть у кого под рукой скрипт для построения дерева блокировок на 9.6 ?

я бы не использовал функцию, а комбинацию ARRAY и generate_series если функция необходима, то лучше sql: CREATE OR REPLACE FUNCTION generate_range (INT, INT) RETURNS INT[] LANGUAGE SQL IMMUTABLE AS $func$ SELECT ARRAY (SELECT generate_series ($1, $2)); $func$;

спасибо, отлично работает

спасибо, отлично работает

рад помочь

Коллеги, подскатиже есть у кого под рукой скрипт для построения дерева блокировок на 9.6 ?

SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid WHERE NOT blockedl.granted AND blockinga.datname='DATABASE'; CREATE VIEW lock_monitor AS( SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname = current_database() );

спасибо, но это так или иначе 10-15 сек, а на том же mysql это меньше секунды

order by field добавьте, limit без order by - ат-ат-ат

select distinct field from table where field is not null limit 20;

спасибо, но это так или иначе 10-15 сек, а на том же mysql это меньше секунды

давайте \d ваша таблица , количество строк в ней и explain analyze select distinct field from table ...

давайте \d ваша таблица , количество строк в ней и explain analyze select distinct field from table ...

1) trans_date | date | not null Indexes: "trans_date_idx" btree (trans_date) 2) ~48 млн

я дошёл примерно досюда: kom@junocat gevel % make USE_PGXS=1 gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fdebug-prefix-map=/build/postgresql-9.6-999=. -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer -fpic -I. -I./ -I/usr/include/postgresql/9.6/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.6 -c -o gevel.o gevel.c gevel.c: In function ‘gin_statpage’: gevel.c:1381:6: error: ‘emptyDataPages’ undeclared (first use in this function) emptyDataPages++; ^~~~~~~~~~~~~~ gevel.c:1381:6: note: each undeclared identifier is reported only once for each function it appears in <builtin>: recipe for target 'gevel.o' failed make: *** [gevel.o] Error 1

Проблема была в том, что Федор суров и сейчас в последнем коммите мастера у него лежит работающая только с 10 постгресом версия (но не собирающаяся под 9.6). Нужно выбрать снапшот для 9.6. Поэтому, алгоритм такой: 1. скачать и распаковать gevel.tar.gz с http://www.sigaev.ru/git/gitweb.cgi?p=gevel.git;a=snapshot;h=bd8b8b031a8049a6e7c18c00946bfbd99d75d27f;sf=tgz 2. USE_PGXS=1 make 3. USE_PGXS=1 make install 4. psql your_database < /usr/share/postgresql/9.6/contrib/gevel.sql 5. Подключаемся, проверяем функционал из http://www.sai.msu.su/~megera/postgres/gist/gevel/README.gevel 6. PROFIT!

1) trans_date | date | not null Indexes: "trans_date_idx" btree (trans_date) 2) ~48 млн

WITH RECURSIVE t AS ( (SELECT trans_date FROM YOUR_TABLE ORDER BY trans_date LIMIT 1) UNION ALL SELECT (SELECT trans_date FROM YOUR_TABLE WHERE trans_date > t.trans_date ORDER BY trans_date LIMIT 1) FROM t WHERE t.trans_date IS NOT NULL ) SELECT trans_date FROM t WHERE trans_date IS NOT NULL LIMIT 20;

Тогда мне тоже интересно, как стриггерить index only scan (и нужно ли)

был же где-то рецепт с recursive cte для этого.

WITH RECURSIVE t AS ( (SELECT min(val) AS val FROM test) UNION ALL SELECT (SELECT min(val) FROM test WHERE val > t.val) AS val FROM t WHERE t.val IS NOT NULL ) SELECT val FROM t WHERE val IS NOT NULL;

ну так distinct отродясь болеет в pg, хотя он стал гораздо лучше, чем раньше. вначале он у вас выгребает из индекса все 48кк строк, а потом из них делает unique limit 20. в общем, в pipeline mode он не работает. а вот group by - прекрасно работает. попробуйте select trans_date from transactions group by trans_date limit 20;

ну так distinct отродясь болеет в pg, хотя он стал гораздо лучше, чем раньше. вначале он у вас выгребает из индекса все 48кк строк, а потом из них делает unique limit 20. в общем, в pipeline mode он не работает. а вот group by - прекрасно работает. попробуйте select trans_date from transactions group by trans_date limit 20;

сам проверил, сам посмеялся над собственной самоуверенностью. group by тоже всех группирует, а потом только отрезает 20

можно же в два действия с CTE, не?

«Наиболее серьезной возможностью я, конечно, считаю мультимастер», — Иван Фролков о разработке Postgres Pro EE

Так все-таки мультимастер нужен? ;)

WITH RECURSIVE t AS ( (SELECT min(val) AS val FROM test) UNION ALL SELECT (SELECT min(val) FROM test WHERE val > t.val) AS val FROM t WHERE t.val IS NOT NULL ) SELECT val FROM t WHERE val IS NOT NULL;

Я хочу сказать - вау! Очень крутая идея в запросе. Запрос отрабатывает с бешенной скоростью, хотя возвращает не рандомные заначения explain analyze with recursive t as ( (select min(trans_date) as trans_date from transactions) union all select ( select min(trans_date) from transactions where trans_date > t.trans_date ) as trans_date from t where t.trans_date is not null ) select trans_date from t where trans_date is not null limit 20;

Так все-таки мультимастер нужен? ;)

продаётся, наверное, неплохо

WITH RECURSIVE t AS ( (SELECT trans_date FROM YOUR_TABLE ORDER BY trans_date LIMIT 1) UNION ALL SELECT (SELECT trans_date FROM YOUR_TABLE WHERE trans_date > t.trans_date ORDER BY trans_date LIMIT 1) FROM t WHERE t.trans_date IS NOT NULL ) SELECT trans_date FROM t WHERE trans_date IS NOT NULL LIMIT 20;

спасибо, этот и вариант за ним оба за 5 сек работают, это уже юзабельно

спасибо, этот и вариант за ним оба за 5 сек работают, это уже юзабельно

видимо очень много уникальных значений, если их относительно немного то должно работать шустрее, подробнее тут можно почитать https://wiki.postgresql.org/wiki/Loose_indexscan

Остался один подписчик до круглого числа в 1024 ))

как всегда юбилей проходит быстро )))

))

Надо одного кикнуть просто?

О достопочтенный джин, трачу ещё одно желание из 999 - не кикай меня)

?

видимо очень много уникальных значений, если их относительно немного то должно работать шустрее, подробнее тут можно почитать https://wiki.postgresql.org/wiki/Loose_indexscan

550 где-то, спасибо, почитаю

простите за вопрос лентяя. нет ли у кого готового sql-скрипта для поиска всех таблиц в базе имеющих определённое поле? ^_^

простите за вопрос лентяя. нет ли у кого готового sql-скрипта для поиска всех таблиц в базе имеющих определённое поле? ^_^

что это за страшный запрос, которому не хватает columns? что хотите то?

нет страшного запроса, есть imposm3 и его внутренняя магия ) хочу в консоли не имея представления о структуре базы выяснить имена таблиц в которых встречается колонка "name"

посмотрите в information_schema.columns

спасибо, всё просто конечно select table_name from information_schema.columns where column_name = 'name' and table_name not like 'pg_%'; лень с позором изгнана =)

Кто все эти люди?..

HR'ы наверное

Ну что вы, долой стереотипы! Это просто девушки, которым интересен постгрес

ERROR: S client not available

Предлагаю сразу на входе тест проводить ))

Экспресс тестирование ?

Вы сомневаетесь, что это девушки?

Уровни изоляции? ))

Расшифровка ACID

Интересно как они рекрутировать планируют ?

Через телеграмм

Расшифровка ACID

CAP-теорема со всеми отягчающими ;)

Для них это просто канал где пасутся Pg DBA

подозреваю что это просто боты. HR по воскресеньям в чаты не ходят

а вот ботов можно огородить простой текстовой капчей 2+7=?

Они ещё ни слова здесь не написали

Это можно

Ждём когда напишут

светка11 еще и в девопсе сидит например

Тогда ей ещё и модель оси

гг

Расшифровка ACID

Расширенная, с примерами, например, разными.

подозреваю что это просто боты. HR по воскресеньям в чаты не ходят

оне разные бывают, точно говорю. Запросто могут в воскресенье ... эээ... охотиться за головами, ага...

Ага, разные примеры к durability

а кто был на pgconf - вам удалось найти применение какому-нибудь из инструментов на слоне-брелке?

Очень полезная штука!)

Очень полезная штука!)

а кроме открывашки? :)

Отвёрткой очень удобно всякие упаковки вскрывать)

ага

я сегодня на нём искал, но не нашёл, ключ на 15 и шестигранник и задумался, что не встречаю в окружающей меня жизни ключей тех размеров, что там есть :)

показали бы хоть

Бот