на связанные поля отдельные индексы нужно делать?

Или это по умолчанию индекс?

это можно выяснить прямо в базе, посмотрев, есть ли на них индексы

постгрес невидимых индексов сам не строит

Ну то есть получается что связи таблиц не равно индекс

Получается надо добавить индексы по всем связанным полям, чтобы быстрее работало?

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

да

верно лши я понимаю, что при связи таблиц мне сразу надо и индекс создавать, а не только перед удалением

Чтобы и добавление/обновление записи было быстрее

нет, тупость ляпнул.

По идее тогда родительские таблицы удут по уникальному индексу искаться

Индексы - наше все! А правильно подобранные и созданные индексы - теплый сон разраба )))

Еще вопрос, удаление в принципе будет редко

1 сек опишу

Правильно ли я сделаю? если будет: DO создание временных индексов по связанным таблицам удаление записей END

Индексы - наше все! А правильно подобранные и созданные индексы - теплый сон разраба )))

индексы - это хак. ISO SQL их вообще не упоминает. по хорошему их быть не должно, а система должна сама всё прекрасно понимать. :P

Правильно ли я сделаю? если будет: DO создание временных индексов по связанным таблицам удаление записей END

оно будет работать, но ничем не будет отличаться от create / delete / drop, кроме времени жизни транзакции, которое лучше всегда держать поменьше

оно будет работать, но ничем не будет отличаться от create / delete / drop, кроме времени жизни транзакции, которое лучше всегда держать поменьше

Ты вот как равному сейчас объяснил. А я значительно ниже рангом :)

Ты вот как равному сейчас объяснил. А я значительно ниже рангом :)

все локи, что ты соберёшь между BEGIN и COMMIT, будут отпущены только по COMMIT. а это иногда может неожиданно тормознуть намертво всё вокруг :)

индексы - это хак. ISO SQL их вообще не упоминает. по хорошему их быть не должно, а система должна сама всё прекрасно понимать. :P

Окружающая среда говорит нам о обратном и ИСО плавно пролетает мимо, как бы нам не хотелось иного.

все локи, что ты соберёшь между BEGIN и COMMIT, будут отпущены только по COMMIT. а это иногда может неожиданно тормознуть намертво всё вокруг :)

Теперь ясно. спасибо за разъяснение.

Окружающая среда говорит нам о обратном и ИСО плавно пролетает мимо, как бы нам не хотелось иного.

почему планировщик запроса, увидев seq scan и = в запросе, не может сам создать btree индекс, если это prepared statement, например? :)

да и без prepared

почему планировщик запроса, увидев seq scan и = в запросе, не может сам создать btree индекс, если это prepared statement, например? :)

Коллега, это не ко мне вопрос. Нечто подобное и более того, я приодически задаю сам себе, уверяю вас. )))

Но, читаешь всякие статьи о тюнинге, оптимизации и всюду лейб мотив,- "правильные идексы" и .т.д. При этом, речь о "основных реализациях серверов БД": оракл, сайбэс, постгре, мускуль )))

все привыкли создавать индексы, и думают, что так и надо

Вот и возникает очевидный вопрос: "Сговорились?" Не? Или все-же ищем ответ глубже...

чем это лучше хинтов планировщика?

Не готов дать однозначный ответ.

Но всеравно долго

а второй раз?

ну, вычитать-то всё с винчестера разок надо

Еще вопрос

По идее должно быть 7900

Seq Scan вычитал всю таблицу и почти всю её выкинул

да, у тебя 402680 строк в таблице с locality != 1 и 7797 c locality = 1

А, ну понял. Короче он 402680 исключил из удаления

Спасибо большое. Теперь работает как субд, а не как XML файл :)

В плане скорости

@Komzpa напомни пожалуйста правило составления навзания индекса

idx_{table}_{index fields} ?

idx в конце

ясно

спасибо

https://habrahabr.ru/post/203386/ https://www.postgresql.org/docs/current/static/sql-createindex.html

https://habrahabr.ru/post/203386/ https://www.postgresql.org/docs/current/static/sql-createindex.html

https://www.depesz.com/2013/04/16/explaining-the-unexplainable/ https://www.depesz.com/2013/04/27/explaining-the-unexplainable-part-2/

??

подскажите, как правильно удалять индекс в нагруженной запросами таблице? по идее должен помочь drop index concurrently, но он вешает ShareUpdateExclusiveLock, что тоже крайне неприятно.

то есть я правильно понимаю, что пока висит drop index concurrently, автовакуум не придет в таблицу? и кстати, как оно вообще работает под капотом? оно может долго висеть и мне интересно, какие условия необходимы, чтобы оно выполнилось

все, я страшно тупил - у коллеги висел AccessShare лок на таблицу, поэтому drop concurrently и не выполнялся. всем спасибо

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

В ответах везде regexp_replace используются. М.б. есть проще решение?

https://pgxn.org/dist/pg_natural_sort_order/ разве проще?)

С кирилицей рабоатет?

судя по исходникам, должен

ERROR: S client not available

она для 9,5

Говорит следующее: Server is version 9.6, library is version 9.5.

http://www.postgresql-archive.org/Natural-sort-order-td5082405.html Нечто страшное есть, но не производиельное судя по общению

Больше ничего не нашел

Нужно то как: 2 2А 3 4 .. 22

Вот такая конструкция title::bytea более менее нормально отдает.

Но опять же буквы ниже цифр.

Ну и 12 выше 2 почемуто

оно их по байтам сортирует. откуда там быть natural?

Это я понял, но почему 12 выше 2

Вот это озадачило

в числе 12 байт меньше чем в 2

?

ээ.. title - это строка

сорян

байтик, отвечающий за символ "1", находится раньше, чем байтик, отвечающий за символ "2"

?

спасибо

всем привет, кто-нибудь может подсказать - в запросе вывожу несколько полей, в том числе 2 поля count(). таблицы через left join. Проблема: count выводит неправильно, он их перемножает между собой и выводит результат в оба поля. запрос : select inc.incident_time::date as data, inc.incident_time::time as time, inc.id as incidentId, emp.id as employeeId, emp.login as login, emp.description as description, COALESCE(inc.hurt_number, 0) as countHurt, count(work.incident) as countWork, count(service.incident) as countService, string_agg(distinct gos.short_title, '; ') as service from incident as inc left join employee as emp on inc.employee = emp.id left join incident_work_off as work on work.incident = inc.id left join incident_gos_service as service on inc.id=service.incident left join gos_service as gos on service.gos_service = gos.id where incident_time between '2017-04-10 00:00:00' and '2017-05-25 23:59:59.999' group by incidentId, employeeId order by incidentId asc

count work и countService перемножаются между собой и выводится результат в оба поля

по отдельности, если убрать эти поля и join-ы на них - все норм

Count считает в итоговом результате

Считай до join-a, например

и джойни два подзапроса

всем привет, кто-нибудь может подсказать - в запросе вывожу несколько полей, в том числе 2 поля count(). таблицы через left join. Проблема: count выводит неправильно, он их перемножает между собой и выводит результат в оба поля. запрос : select inc.incident_time::date as data, inc.incident_time::time as time, inc.id as incidentId, emp.id as employeeId, emp.login as login, emp.description as description, COALESCE(inc.hurt_number, 0) as countHurt, count(work.incident) as countWork, count(service.incident) as countService, string_agg(distinct gos.short_title, '; ') as service from incident as inc left join employee as emp on inc.employee = emp.id left join incident_work_off as work on work.incident = inc.id left join incident_gos_service as service on inc.id=service.incident left join gos_service as gos on service.gos_service = gos.id where incident_time between '2017-04-10 00:00:00' and '2017-05-25 23:59:59.999' group by incidentId, employeeId order by incidentId asc

Я вообще удивлён, что запрос работает. В полях, по которым идёт группировка, нет data,time, login, description и counthurt - почему оно не ругается при разборе, кстати?

Я вообще удивлён, что запрос работает. В полях, по которым идёт группировка, нет data,time, login, description и counthurt - почему оно не ругается при разборе, кстати?

не знаю даже) запрос работает, только count считает неправильно. я в pgadmin составляю запросик

А ожидается, что под одним select'ом 2 вызова функции count без distinct вернут разный результат?

если в полях есть null'ы, то вернут. но тут явно не тот случай.

А ожидается, что под одним select'ом 2 вызова функции count без distinct вернут разный результат?

по сути countService выводит количество значений из service, и когда происходит вывод неправильного результата, в service тоже идет куча одинаковых данных.