Возможности PostgreSQL для тех, кто перешел с MySQL

    Крутой varanio буквально на прошлой неделе прочитал на DevConf забойный доклад для всех кто пересел на Посгрес с MySQL, но до сих пор не использует новую базу данных в полной мере. По мотивам выступления родилась эта публикация.


    Мы рады сообщить, что подготовка к PG Day'17 Russia идет полным ходом! Мы опубликовали полное расписание предстоящего мероприятия. Приглашаем всех желающих прийти и похоливарить с Антоном лично



    Поскольку доклад на DevConf вызвал в целом положительные отзывы, я решил оформить его в виде статьи для тех, кто по каким-то причинам не смог присутствовать на конференции.


    Почему вообще возникла идея такого доклада? Дело в том, что PostgreSQL сейчас явно хайповая технология, и многие переходят на эту СУБД. Иногда — по объективным причинам, иногда — просто потому что это модно.


    Но сплошь и рядом складывается такая ситуация, когда какой-нибудь условный программист Вася вчера писал на MySQL, а сегодня вдруг начал писать на Посгресе. Как он будет писать? Да в целом также, как и раньше, используя лишь самый минимальный набор возможностей новой базы. Практика показывает, что проходят годы, прежде чем СУБД начинает использоваться более менее полноценно.


    Не холивар


    Сразу disclaimer: это не статья "мускуль vs посгрес". Переходить на посгрес или нет — ваше дело. Uber, к примеру, перешел обратно на MySQL по своим каким-то причинам.


    Надо отдать должное Oracle, они явно двигают MySQL в правильном направлении. В 5.7 сделали strict mode по умолчанию. В восьмой версии обещают CTE и оконные функции, а также избавление от движка MyISAM в системных таблицах. Т.е. видно, что в базу вкладываются ресурсы, и хотелки юзеров исследуются очень серьёзно.


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


    Встроенные типы данных


    В базу встроено множество типов данных, помимо обычных числовых и строковых. А также операторы для их взаимодействия.


    Например, есть типы cidr, inet, macaddr для работы с ip адресами.


    -- проверяем, входит ли ip адрес '128.0.0.1' в cidr '127.0.0.0/24' 
    -- с помощью оператора &&
    select '127.0.0.0/24'::cidr && '128.0.0.1';
    -- вернет false

    Или например, время с таймзоной (timestamptz), интервал времени и т.д.


    -- Сколько сейчас времени в Нью-Йорке?
    SELECT NOW() AT TIME ZONE 'America/New_York';
    
    -- Сколько часов разницы сейчас между Москвой и Нью-Йорком?
    SELECT NOW() AT TIME ZONE 'America/New_York' 
         - NOW() AT TIME ZONE 'Europe/Moscow';
    -- результат: -07:00:00  

    Когда я готовил этот слайд, я решил из любопытства посмотреть, а какое смещение времени относительно UTC было 100 лет назад, в 1917 году:


    select '1917-06-17 00:00:00 UTC' at time zone 'Europe/Moscow';
    -- результат: 1917-06-17 02:31:19

    Т.е. москвичи жили по времени UTC+02:31:19.


    Кроме перечисленных, есть и другие встроенные типы данных: UUID, JSONB, XML, битовые строки и т.д.


    Тип array


    Отдельно надо рассмотреть тип "array". Массивы давно и хорошо интегрированы в PostgreSQL. Многомерные массивы, слайсы, операторы пересечения, объединения и т.д. Существует множество функций для работы с массивами.


    --- Пример проверки пересечения массивов
    SELECT ARRAY [1, 2, 8, 10] && ARRAY [1, 2, 3, 4, 5];
    
    --- Входит ли один массив в другой?
    SELECT ARRAY [1, 2] <@ ARRAY [1, 2, 3, 4, 5]

    Есть очень удобная функция, которая так и называется: array. В качестве аргумента подается некий SELECT-запрос, на выходе — результат запроса в виде массива.


    Есть и обратная функция: unnest. Она берет массив и возвращает его как результат запроса. Это бывает удобно, например, когда нужно вставить вручную несколько одинаковых записей с разными id, но не хочется заниматься копипастой:


    INSERT INTO users
      (id, status, added_at)
    
      SELECT user_id, 5, '2010-03-03 10:56:40'
        FROM unnest(array[123, 1232, 534, 233, 100500]) as u(user_id)

    Создаем собственные типы


    Собственные типы можно создавать тремя способами. Во-первых, если вы знаете язык Си, то вы можете создать базовый тип, наравне с каким-нибудь int или varchar. Пример из мануала:


    CREATE TYPE box (
      INTERNALLENGTH = 16,
      INPUT = my_box_in_function,
      OUTPUT = my_box_out_function
    );

    Т.е. создаете пару функций, которые умеют делать из cstring ваш тип и наоборот. После чего можно использовать этот тип, например, в объявлении таблицы:


    CREATE TABLE myboxes (
      id integer,
      description box
    );

    Второй способ — это композитный тип. Например, для хранения комплексных чисел:


    CREATE TYPE complex AS (
      r       double precision,
      i       double precision
    );

    И потом использовать это:


    CREATE TABLE math (
      result complex
    );
    
    INSERT INTO math
      (result)
    
      VALUES
        ((0.5, -0.6)::complex);
    
    SELECT (result).i FROM math;
    -- результат: -0.6

    Третий вид типа, который вы можете создать — это доменный тип. Доменный тип — это просто алиас к существующему типу с другим именем, т.е. именем, соответствующим вашей бизнес-логике.


    CREATE DOMAIN us_postal_code AS TEXT;

    us_postal_code — это более семантично, чем некий абстрактный text или varchar.


    Создаем собственные операторы


    Можно делать свои операторы. Например, сложение комплексных чисел (сам тип complex мы определили выше):


    -- описываем функцию сложения, например, на языке SQL
    CREATE OR REPLACE FUNCTION sum_complex(x COMPLEX, y COMPLEX) 
    RETURNS COMPLEX AS $$
      SELECT x.r + y.r, x.i + y.i;
    $$ language sql;
    
    -- создаем оператор "плюс" для комплексных чисел
    CREATE OPERATOR +
    (
      PROCEDURE = sum_complex,
      LEFTARG = COMPLEX,
      RIGHTARG = COMPLEX
    );

    Создаем собственные правила для преобразования типов


    Давайте сделаем какой-нибудь сферический в вакууме пример. Создадим типы RUR и USD, и правило для преобразования одного типа в другой. Так как я плохо знаю си, то для примера сделаем простой композитный тип:


    CREATE TYPE USD AS (
      sum FLOAT
    );
    
    CREATE TYPE RUR AS (
      sum FLOAT
    );
    
    -- функция преобразования долларов в рубли (по курсу 60, это же сферический пример)
    CREATE FUNCTION usd2rur(value USD)
    RETURNS RUR AS $$
      SELECT value.sum * 60.0;
    $$ LANGUAGE SQL;
    
    -- описываем правило для посгреса, какой тип как "кастить".
    CREATE CAST ( USD AS RUR )
      WITH FUNCTION usd2rur(USD) AS ASSIGNMENT;

    Собственно, это всё, теперь можно использовать. Сколько там будет 100 баксов в рублях?


    select '(100.0)'::usd::rur;

    Результат будет таким:


    rur
    --------
    (6000)
    (1 row)

    Типы в расширениях PostgreSQL


    Существуют расширения, где описаны типы данных и все, что для них нужно. Например, расширение ip4r, описывающее типы для IP-адресов и их диапазонов.


    Если вы посмотрите исходники https://github.com/RhodiumToad/ip4r/blob/master/ip4r--2.2.sql, то увидите, что расширение — это просто, по сути, набор иструкций CREATE TYPE, CREATE OPERATOR, CREATE CAST и т.д.


    Описаны правила индексирования. Например, тип ip4r (диапазон IP-адресов) можно проиндексировать индексом GIST по оператору && (и другим). Таким образом, можно сделать таблицу для поиска городов по IP.


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


    Индексы


    Помимо стандартного btree есть и другие: GIN (можно использовать для некоторых операций с массивами, для jsonb, для полнотекстового поиска), GIST, brin и т.д.


    Partial indexes


    Бывают ситуации, когда у вас 10 миллионов строк в таблице, при чем из них только штук 100, допустим, в статусе "Платеж обрабатывается". И вы постоянно дергаете этот статус "обрабатывается" как-то так: select ... where status = 2.


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


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


    CREATE INDEX my_money_status_idx on my_money(status) WHERE status = 2;

    Этот индекс будет хорошо работать на запросах select * from my_money where status = 2 и при этом занимать мало места.


    Индексы по выражению


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


    CREATE INDEX people_names
    ON people ((first_name || ' ' || last_name));

    И потом такой запрос будет быстро работать:


    SELECT *
    FROM people
    WHERE
    (first_name || ' ' || last_name) = 'John Smith';

    Constraints


    Помимо стандартных UNIQUE и NOT NULL, в базе можно делать еще и другие проверки целостности. В доменном типе можно прописать check:


    CREATE DOMAIN us_postal_code AS TEXT
    CHECK(
      VALUE ~ '^\d{5}$'
      OR VALUE ~ '^\d{5}-\d{4}$'
    );

    который проверяет, что в колонку типа us_postal_code попадут только 5 цифр или 5 цифр, дефис и 4 цифры. Разумеется, сюда можно писать не только регулярки, но и любые другие условия.


    Также check можно прописать в таблице:


    CREATE TABLE users (
      id integer,
      name text,
      email text,
      CHECK (length(name) >= 1 AND length(name) <= 300)
    );

    Т.е. в имени должен быть хотя бы один символ, и не больше 300.


    Вообще говоря, сами типы являются также и неким ограничением, дополнительной проверкой, которую делает база. Например, если у вас есть тип complex (смотри выше), состоящий, по сути, из двух чисел, то вы не вставите туда случайно строку:


    INSERT INTO math (result) VALUES ((0.5, 'привет')::complex);
    ERROR: invalid input syntax for type double precision: "привет"

    Таким образом, иногда композитный тип может быть предпочтительнее, чем jsonb, потому что в json вы можете напихать что угодно вообще.


    Частичная уникальность и уникальность по выражению


    В отличие от простой уникальности UNIQUE или PRIMARY KEY, в посгресе можно сделать уникальность среди определенного набора строк, заданного условием. Например, email должен быть уникальным среди неудаленных юзеров:


    CREATE UNIQUE INDEX users_unique_idx
      ON users(email)
      WHERE deleted = false;

    Еще забавная штука: можно сделать уникальность не по одному полю, а по любому выражению. К примеру, можно сделать так, что в таблице сумма двух колонок не будет повторяться:


    CREATE TABLE test_summ (
      a INT,
      b INT
    );
    
    CREATE UNIQUE INDEX test_summ_unique_idx
      ON test_summ ((a + b));
    
    INSERT INTO test_summ VALUES (1, 2);
    
    INSERT INTO test_summ VALUES (3, 0);
    -- выдаст ошибку уникальности

    Constraint Exclude


    Ключевое слово EXCLUDE позволяет делать так, что при вставке/обновлении строки, эта строка будет сравниваться с другими по заданному оператору. Например, таблица, содержащая непересекающиеся диапазоны IP (проверяется оператором пересечения &&):


    CREATE TABLE ip_ranges (
      ip_range ip4r,
      EXCLUDE USING gist (ip_range WITH &&)
    );

    Вообще, обычный UNIQUE — это, по сути, EXCLUDE с оператором =.


    Хранимые процедуры


    Хранимые процедуры можно писать на SQL, pl/pgsql, javascript, (pl/v8), python и т.д. Например, можно на языке R обсчитать какую-то статистику и вернуть из нее график с результатом.
    Это отдельная большая тема, советую поискать доклад Ивана Панченко на этот счет.


    CTE (Common Table Expressions)


    Это будет и в MySQL 8, но всё равно давайте кратко остановимся на этом.


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


    WITH  subquery1 AS (
      SELECT ... -- тут куча всяких условий и тд.
    ),
    subquery2 AS (
      SELECT ... -- тут тоже куча условий, группировок
    )
    SELECT *  -- начался основной запрос
      FROM subquery1
        JOIN subquery 2
      ON ...

    С точки зрения оптимизации запросов, нужно учитывать, что каждый такой CTE-подзапрос выполняется отдельно. Это может быть как плюсом, так и минусом.


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


    И наоборот, если вы решили просто для читабельности вынести часть запроса в CTE, то иногда это может выйти для вас боком.


    В CTE можно использовать не только SELECT-запросы, но и UPDATE.


    Пример: обновить юзеров с возрастом > 20 лет, и в том же запросе выдать имена обновленных вместе с какой-нибудь там страной.


    with users_updated AS (
      UPDATE users
        SET status = 4
        WHERE age > 20
        RETURNING id
    )
    
    SELECT name, country
      FROM users
      JOIN countries
        ON users.country_id = countries.id
      WHERE id IN (
      SELECT id
        FROM users_updated
    );

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


    WITH
    update1 AS (
      UPDATE test
      SET money = money + 1
    ),
    
    update2 AS (
      UPDATE test
      SET money = money - 1
    )
    
    SELECT money FROM test;

    Кажется, что мы прибавили рубль, потом отняли рубль, и должно остаться всё как есть.


    Но дело в том, что update1 и update2 при своем выполнении будут брать начальную версию таблицы, т.е. по сути получится так, что один update затрет изменения другого. Поэтому с update внутри CTE надо точно знать, что ты делаешь и зачем.


    Оконные функции


    Про оконные функции я уже когда-то подробно писал здесь: https://habrahabr.ru/post/268983/. Оконные функции тоже обещают в MySQL 8.


    Разное


    FILTER


    К агрегатным функциям (например, COUNT или SUM), можно дописывать условие FILTER, т.е. агрегировать не все строки, а только ограниченные неким выражением:


    SELECT
      count(*) FILTER (WHERE age > 20) AS old,
      count(*) FILTER (WHERE age <= 20) AS young
    
    FROM users;

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


    \watch


    Все знают, что в psql есть команды для просмотра разных объектов, например \d, \dt+ и т.д.
    Есть особая команда, называется \watch. Т.е. вы выполняете запрос, потом пишете
    \watch 5 и ваш запрос будет выполняться каждые 5 секунд, пока не отмените.
    Это работает не только с select, но и с любым другим, например с update (например, когда нужно большую таблицу медленно обновить по чуть-чуть).


    Materialized View


    Это как View, только закешированное (материализованное). Кеш можно обновлять с помощью команды REFRESH MATERIALIZED VIEW. Есть также ключевое слово CONCURRENTLY, чтобы Postgres не лочил при обновлении SELECT-запросы.


    Listen / Notify


    Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях). Суть в том, что можно подписаться на какое то событие, а также можно уведомить подписчиков, что событие произошло, передав при этом строку с доп. сведениями.


    FDW


    Механизм Foreign Data Wrappers позволяет использовать некоторые внешние данные, как простые таблицы. Т.е. к примеру, можно заджойнить постгресовую таблицу, мускульную таблицу, и csv файл.


    Sequences


    SEQUENCE — это посгресовый аналог MySQL-ного AUTO_INCREMENT. В отличие от MySQL, sequence может существовать отдельно от таблиц или наоборот, "тикать" сразу для нескольких таблиц. Можно задавать различные параметры, например, размер инкремента, зацикливание и проч.


    Вместо выводов


    Это верхушка айсберга, на самом деле. Есть еще куча нюансов, вообще никак не затронутых в статье, потому что на всё никакой статьи не хватит. По одним только хранимым процедурам можно написать книгу. Или посмотрите, к примеру, полный список sql-команд текущей версии: https://www.postgresql.org/docs/9.6/static/sql-commands.html


    Главное, что я хотел показать в статье: несмотря на хайповость, PostgreSQL — очень старая СУБД, в которой очень много чего есть, и которая очень хорошо расширяется. Поэтому при переходе на нее с MySQL рекомендуется полистать мануал, почитать статьи и т.д.

    PG Day'17 Russia
    0.00
    Company
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 139

      –5
      обычно ORM поддерживают только смежные фичи… возможно из-за этого uber перешел обратно на MySQL)
        +5
        Убер перешел на MySQL потому что им нужна была не RDBMS, а платформа для реализации своей key-value базы: https://eng.uber.com/schemaless-part-one/
        +5

        Очень важная, на мой взгляд, вещь, которая есть в постгресе и нет в мускуле — транзакционный DDL. Экономит просто нереальное кол-во нервных клеток. Constraints тоже классная и нужная вещь, которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение. Ну и конечно умение генерировать series. Ну и в целом постгрес рулит — уж очень он функциональный и продуманный.

          +1

          В MySQL 8 уже перешли на транзакционный DDL.


          С трудом представляю себе, как они это сделали: в исходниках MySQL системные словари прибиты к MyISAM гвоздями с незапамятных времен. Явно пришлось переработать огромные залежи очень старого кода.

            +1

            Ну, насколько я понял, от myisam было решено отказаться. А так, это хорошая новость. Кстати, я один не понимаю такую систему версионирования: 5.7.х -> 8.0.0? :)

              +1
              Убрали первую цифру. Как Java или Linux Kernel.
            • UFO just landed and posted this here
                0

                В Оракле нет транзакционного DDL. (Если есть, подскажите как включить, Oracle после PostgreSQL — это адская боль и мучение)

                  0
                  Включить нельзя, вы правы, его там нет.
              +1
              Constraints которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение.
              скажите, кратко, что за проблемы с Constraints в MySQL? что значит на уровне интерфейса?
                0
                Я так понимаю это неработающие CHECK
                  +1
                  The CHECK clause is parsed but ignored by all storage engines
                  да это кончено жесть
                    0

                    Имеет место такая жесть, да.
                    Но это относится только к check, foreign keys итд работают.

                      0
                      foreign keys итд работают.

                      Да и те как-то хитро сделаны
                      MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.

                      create table testfk (
                         parent_id int not null references testbase(id)
                      ) engine=innodb;
                      

                      Вот так сделать синтаксически верно, а FK просто тихо создано не будет
                        +1

                        Ага, надо в constraint писать. Там какая-то проблема "курицы и яйца" у них в этом месте.

              –1
              В каком контексте в статье употребляется слово «хайповость», «хайповая технология»?
              Что оно отражает?
                +1
                Ну, это что-то модное. То, о чем все говорят на конференциях. То, что все используют в новых проектах и т.д. Нелегаси
                  –2
                  Спасибо за ответ.
                  Гугление выдало в том числе значение — пирамида (с англ. HYIP — High Yield Investment Program) имеющая не самый положительный смысл.
                  Также трудно PG назвать новым проектом.
                  На конференции по PG конечно будут говорить о PG. Но это одна конференция.

                  Хотелось бы комментарий автора статьи услышать.
                    +2
                    Я и есть автор статьи. Ее просто опубликовали в рамках блога конференции pgday

                    http://www.urbandictionary.com/define.php?term=hype
                      0
                      Спасибо за ответ.
                –10
                все идет к тому, что мускул и постгрес станут похожими на mssql аж 2005 (!) года выпуска

                  +6
                  У них, кроме всего прочего, есть одно огромное преимущество перед MS SQL Server'ом — не надо платить 800 килорублей за лицензию.
                    –2
                    первое и главное, на мой взгляд, преимущество — низкий порог вхождения. а килорубли проблема бизнеса, не моя
                      +3

                      С такой логикой можно далеко зайти. "Написал O(N^3) алгоритм, работает, а что на сервера надо много килорублей — проблема бизнеса".

                        –1
                        Так сейчас частенько так и делают. Если затраты на оптимизацию выше стоимости железа помощнее, то почему нет.
                        +2
                        Правильно ли я понял, что ваше нежелание/неумение разбираться должно стоить бизнесу 800k? Кроме того, PL/pgSQL дался мне немного проще, чем T-SQL, как и нюансы администрирования СУБД. Или вы вхождением называете умение запускать Management Studio и жать в ней кнопочки в правильном порядке?
                        • UFO just landed and posted this here
                            +1

                            Oracle после PostgreSQL — боль. Транзакционного DDLя нет, sqlplus — жалкое подобие консольного клиента, вообще не чета psql'ю (а есть куда более продвинутые, pgcli, например), типов данных из коробки — раз два и обчёлся (CLOB'ы — это вообще тихий ужас). Многие запросы пишутся через жопу (уже спустя год с содроганием вспоминаю вездесущие SELECT FROM DUAL).
                            Возможно, это дело привычки. С Постгресом я уже много лет работаю, а с Oracle год повеселился (и, надеюсь, больше ни-ни).
                            Но есть и плюсы: многие вещи в Оракле есть из коробки. Полнотекстовый поиск? Пожалуйста (+$100500), геометрия/география? Вот тебе бесплатный Oracle Locator и платный Oracle Spatial (нам, кстати, хватило бесплатного).
                            Но берёшь PostgreSQL, добавляешь в него по вкусу PostGIS, pg_pathman, мониторинг хороший (тут можно попиарить okmeter), приправляешь каким-нибудь barman'ом — и становится даже лучше.

                            +1
                            Скорее всего вы не человек, могущий реально защитить диплом инженера-программиста в части, как минимум, техникой экономического обоснования.
                              0
                              У бакаларвских дипломов больше нет обязательной экономической части.
                            +2

                            Вы забыли про возможность запускать под нормальными системами, а не под вендой.

                            +2
                            Не надо грязи. Я сейчас живу на MS SQL 2005. Это боль. Не такая большая, как 2000, но ощутимая.
                              –1
                              а вы с чем сравниваете, боль по сравнению с чем?
                              Если можно, несколько пунктов которые в другой СУБД лучше.
                                +5
                                Например — нет кастомных типов, нет enum, нет логического типа. Мне уже по ночам снятся CASE WHEN expr = 1 THEN.
                                Синтаксис T-SQL… не так очевиден, как PL/pgSQL. Триггеры в постгресе пишутся намного проще.

                                Нет юникода, как я забыл! Основная причина того, что мы до сих пор на 1251 — невозможность utf8 в субд.
                                  0

                                  А с utf-8-то что не так?..

                                    +1
                                    sql 2005 снят с поддержки.
                                    пользовательские типы были в 2008, я с ними работал, функциональные возможности типов не помню.
                                    тип bit есть, не знаю с какой версии.
                                    юникодные NCHAR NVARCHAR был, но на данный момент я не помню какая связь с collation
                                      0
                                      > sql 2005 снят с поддержки.

                                      Прочтите исходный комментарий ветки.

                                      > тип bit есть

                                      Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?

                                      > юникодные NCHAR NVARCHAR

                                      Это не тот юникод. Совсем не тот, поверьте.
                                        0
                                        Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?
                                        я не уверен, хотелось бы узнать, наверно какое то удобство в чем то
                                          0
                                          CASE WHEN 2>1 THEN bit_field=1 ELSE bit_field=0 END
                                            +2

                                            Булев (логический) тип — это то, что возвращают операторы сравнения, также его принимают и возвращают логические операторы.


                                            bit — это целочисленный тип с двумя возможными значениями.


                                            В SQL нельзя написать WHERE foo, если foo имеет тип bit. Приходится писать WHERE foo=1. В обратную же сторону получается еще хуже — вместо SELECT a<b as cmp нужно писать SELECT case when a<b then 1 else 0 end as cmp.

                                              0
                                              ага, действительно гадость
                                                0
                                                С тремя возможными значениями:
                                                An integer data type that can take a value of 1, 0, or NULL.
                                              0
                                              Это не тот юникод. Совсем не тот, поверьте.
                                              Можете сказать что не так с юникодом?
                                                0
                                                UTF16 не так.
                                                  +1
                                                  UTF16 это проблема для какого то клиента? Для PHP?
                                                    0
                                                    Если вам доставляет извращённое удовольствие регулярно перекодировать в utf8 и обратно — я рад за вас. Мне не доставляет ни малейшего. Очень весело, например, собирать json. Да и разбирать часто не менее весело.
                                                      0
                                                      Вам видимо кажется что я прикалываюсь, но нет, мне действительно интересно какие у кого проблемы с MS SQL. Я MS SQL с PHP не использовал, потому о проблемах с кодировкой юникода слышу в первые. Я предполагал что можно задавать кодировку, с которой подключаешься к MS SQL.
                                                        –1
                                                        Причем тут PHP? Этот язык, к вашему сведению, вообще понятием «кодировка» не оперирует. Для него что request, что response — просто набор байт. И не более того.

                                                        Вам прямо говорят, что JSON, например, это UTF-8. Независимо от языка.
                                                          0

                                                          Ничего подобного, JSON может быть в любой кодировке. Стандарт вообще ничего не говорит про бинарное представление JSON, только про текстовое.


                                                          A JSON text is a sequence of tokens formed from Unicode code points that conforms to the JSON value grammar.
                                                          Final draft of the TC39 "The JSON Data Interchange Format" standart, пункт четвертый.
                                                        0

                                                        Значит, нужен слой, который это перекодирование сделает сам, вот и все.

                                        +1
                                        MSSQL это головная боль для админов.
                                          0

                                          А у админов-то какие с ним проблемы?..

                                            0
                                            Насчет dba админов не знаю, а для сисадминов обыкновенных™ mssql на порядок удобнее в плане бэкапов/восстановления. А остальное их не касается.
                                            0
                                            В чем именно похожими?
                                            Слишком общая фраза, так же общо отвечу: у SQL Server даже 2017 нет многих возможностей, которые в PostgreSQL были уже давно.
                                            +1

                                            Самый распространенный вариант частичного индекса (email + not deleted) в mysql можно изобразить через виртуальные столбцы. Синтаксис виртуальных столбцов несколько отличается в mariadb и oracle mysql (где они появились позднее), но суть та же. Покажу на примере mariadb.


                                            CREATE TABLE users (
                                                ...
                                                email varchar(255),
                                                is_deleted boolean NOT NULL DEFAULT FALSE,
                                                _null_if_deleted char(0) AS (IF(is_deleted, NULL, '')) PERSISTENT,
                                                ...
                                            );
                                            CREATE UNIQUE INDEX uniq_users_email ON users(email, _null_if_deleted);

                                            Трюк в виртуальном char(0) поле, которое может иметь всего 2 значения — пустая строка и null. Если пользователь удален, то там будет null, и в итоге строка в uniq_users_email участвовать не будет.


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

                                              0
                                              то там будет null, и в итоге строка в uniq_users_email участвовать не будет
                                              а почему? из за слова PERSISTENT?
                                              непонятно как char(0) при null должен исключаться от попадания в индекс
                                                0

                                                Из-за слова NULL. NULL-значения не участвуют в unique index. В случае индекса по нескольким полям — если хотя бы одно из них NULL. char(0) — это просто для экономии места, чтобы не хранить никакое значение, которое тут все равно служебное и смысла не несет.

                                                  +2
                                                  для меня это неожиданное поведение, в ms sql 2016 null колонки попадают в индекс и значения двух колонок (null, null) заблокируют вставку следующей пары (null, null), так же как и например
                                                  (null, 1) заблокируют вставку следующей пары (null, 1)

                                                  вот можно поиграться
                                                  USE [test]
                                                  GO
                                                  
                                                  /****** Object:  Table [dbo].[users]    Script Date: 6/23/2017 1:31:19 PM ******/
                                                  SET ANSI_NULLS ON
                                                  GO
                                                  
                                                  SET QUOTED_IDENTIFIER ON
                                                  GO
                                                  
                                                  CREATE TABLE [dbo].[users](
                                                  	[id] [int] NOT NULL,
                                                  	[email] [varchar](255) NULL,
                                                  	[is_deleted] [bit] NULL,
                                                   CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
                                                  (
                                                  	[id] ASC
                                                  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                  ) ON [PRIMARY]
                                                  GO
                                                  
                                                  
                                                  USE [test]
                                                  GO
                                                  
                                                  SET ANSI_PADDING ON
                                                  GO
                                                  
                                                  /****** Object:  Index [uniq_users_email]    Script Date: 6/23/2017 1:31:32 PM ******/
                                                  CREATE UNIQUE NONCLUSTERED INDEX [uniq_users_email] ON [dbo].[users]
                                                  (
                                                  	[email] ASC,
                                                  	[is_deleted] ASC
                                                  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                  GO
                                                  
                                                    +1

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


                                                    В ANSI SQL это четко прописано. В этом весь смысл NULL-а.


                                                    А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?

                                                      0
                                                      CREATE UNIQUE INDEX IX_users_email ON users(email) WHERE email IS NOT NULL
                                                        +2

                                                        Я так и думал. :)


                                                        С точки зрения ANSI SQL это полная жесть, конечно. "Уникальность" NULL-а ломает кучу классических SQL-паттернов, ради которых NULL и задуман таким, какой он в ANSI SQL.

                                                          +1
                                                          «Уникальность» NULL-а ломает кучу классических SQL-паттернов
                                                          а можно огласить весь список? я предпалагаю что ms sql как то должен их обойти без особых проблем
                                                            0

                                                            Полагаю, что для обхода везде придется дописывать where field is not null.


                                                            Скажем, поиск дубликатов:


                                                            > create table a (id serial, v int);
                                                            > insert into a (v) values (1), (null), (2), (null), (1);
                                                            > select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id;
                                                            +----+------+----+------+
                                                            | id | v    | id | v    |
                                                            +----+------+----+------+
                                                            |  1 |    1 |  5 |    1 |
                                                            +----+------+----+------+
                                                            1 row in set (0.00 sec)
                                                              0

                                                              В запросах это решается настройкой ANSI NULLS:


                                                              SET ANSI_NULLS ON

                                                              Причем установить ее можно глобально для базы, тогда не нужно будет писать эту строку каждый раз (хотя все равно пишут, для независимости от настроек базы).


                                                              Неустранимыми "особенностями" обладают лишь те объекты, которые хранятся в базе — индексы и, может быть, ограничения. Индексированные виды при этом внезапно работают в режиме ANSI_NULLS.

                                                                0

                                                                Гм… А что произойдет, если я при ANSI_NULLS ON сделаю UNIQUE INDEX на a.v из примера выше (после того, как зачистил не null-дубликаты)? Все равно придется where not null для индекса дописывать?

                                                                  0
                                                                  хоть ANSI_NULLS ON хоть OFF, на UNIQUE INDEX это не влияет
                                                                0
                                                                в ms sql 2016 так
                                                                select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id;
                                                                
                                                                id	v	id	v
                                                                1	1	5	1
                                                                

                                                                SET ANSI_NULLS on/off ничего не меняет
                                                                  0

                                                                  А, точно. ANSI_NULLS влияет только на интерпретацию сравнений с константой NULL.

                                                                    0

                                                                    А вот выше говорят про настройку ANSI_NULLS. Я верно понимаю, что такой результат выборки получается при ANSI_NULLS ON, а если сделать OFF, то вылезут еще строчки с NULL-ами?

                                                                      0

                                                                      Нет, я там ошибся. Жаль, нельзя себе минус поставить...

                                                                        +4

                                                                        То есть получается, что в MSSQL:
                                                                        1) при сравнении с константой NULL результат зависит от ANSI_NULLS,
                                                                        2) при сравнении значений полей NULL-ы всегда не равны,
                                                                        3) но в UNIQUE INDEX NULL-ы всегда равны.


                                                                        Я все верно усвоил?


                                                                        Мой мозг. :) Зачем, зачем они так сделали?

                                                                          0

                                                                          Legacy, сэр :-)

                                                                            +1

                                                                            И эти люди запрещают нам ковыряться в носу! :-) (В смысле, критикуют strict_mode в mysql).

                                                            0
                                                            А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?
                                                            а мне вот наоборот интересно, как в PG или MySql делать индекс по комбинации где одна колонка заполнена, а другая null
                                                              0

                                                              Вообще, как правило, вы не должны этого хотеть, ведь NULL — это не значение, а отсутствие значения.


                                                              А если очень хочется, можно, конечно, сделать индекс вида (col1, is_null(col2)). В постгресе напрямую, в mysql — тем же трюком с virtual columns, только наоборот.

                                                            0

                                                            Хм, проверил — вы правы, в уникальном индексе в MS SQL может быть только 1 NULL. Осталось понять, каким образом я запомнил противоположное?..

                                                              0
                                                              в 2000 вроде было другое поведение, я уже не помню точно, возможно зависит от SET ANSI_NULLS ON или еще от чего нибудь
                                                                0

                                                                ANSI_NULLS можно менять в любое время, а потому эта настройка не может влиять на структуру индекса.

                                                                  0
                                                                  Чисто в теории на структуру индекса может не влиять, а на поведение может. Ожидания могут быть нарушены, но поведение может быть детерминировано.
                                                            0
                                                            а как вы узнали что оно действительно не хранит значений?
                                                              0

                                                              Насчет хранения не уверен, но, как минимум, для выборки по индексу не надо читать ничего из данных:


                                                              MariaDB [test]> create table foo (c0 char(0), index (c0));
                                                              Query OK, 0 rows affected (0.08 sec)
                                                              
                                                              MariaDB [test]> insert into foo values ('');
                                                              Query OK, 1 row affected (0.01 sec)
                                                              
                                                              MariaDB [test]> explain select c0 from foo;
                                                              +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
                                                              | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
                                                              +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
                                                              |    1 | SIMPLE      | foo   | index | NULL          | c0   | 1       | NULL |    1 | Using index |
                                                              +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
                                                              1 row in set (0.01 sec)

                                                              "If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index."


                                                              Как точно узнать, что совсем не хранится, я не знаю. :) Но в случае char(0) выглядит логичным ничего не хранить. Это скорее уже предположение.

                                                                0
                                                                Для nullable char(0) логичным выглядит хранить один бит вне зависимости от наличия индекса, но есть подозрение, что в большинстве случаев это экономия на спичках., даже с индексом.
                                                                  0

                                                                  Я выбирал из char(0) и enum-а с одним возможным значением. char(0) показался эстетичнее, плюс дополнительное соображение о логичности хранения только одного бита (enum-то наверняка всегда хранится как integer).

                                                            +1

                                                            Согласно стандарту ANSI SQL, NULL-значение не может быть равно никакому другому, даже другому NULL (похожим образом ведет себя NaN в IEEE 754). Поэтому ключи, содержащие хотя бы 1 NULL, всегда считаются уникальными.


                                                            Так было сделано для того, чтобы можно было накладывать ограничение уникальности на опциональные поля, иначе в таких ограничениях было бы мало смысла.

                                                          0
                                                          Про enum в кастомных типах забыли: https://www.postgresql.org/docs/current/static/datatype-enum.html
                                                          Это один из самых частых вопросов от свитчеров.
                                                            +1
                                                            а есть какая нормальная IDE к PgSql?
                                                            pgAdmin что то не впечатлил.
                                                            Есть что то бесплатное с аналогом Database Diagram в Management studio?
                                                              0
                                                              Есть DataGrip и он хорош. Но не бесплатен.
                                                                +1

                                                                Меня вполне устраивает Database Tools, который поставляется с практически всеми JetBrains-овскими IDE.

                                                                  +4
                                                                  Попробуйте DBeaver
                                                                    +3
                                                                    psql
                                                                      +1
                                                                      Ну или вот если красиво хочется
                                                                      +1

                                                                      Попробуйте поставить pgAdmin 3, потому что pgAdmin 4 — какое-то невнятное убожество, которое умеет только тормозить и жрать память, потому что написано "на вебе".

                                                                      0
                                                                      Listen / Notify

                                                                      Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях).

                                                                      Мы используем. Связали так php и golang, отправляется уведомление при изменении данных, чтоб бэкенд подхватил. Работает как часы, очень удобно.
                                                                        0
                                                                        golang подписан, а php отправляет уведомления?
                                                                          0
                                                                          Да. PHP меняет данные, сохраняет, отправляет в golang «эй, смотри тут данные обновились».
                                                                            0
                                                                            и сколько соединений с pg держит голанг?
                                                                              0
                                                                              Два. Одно для общения с самой базой, одно для Listen.
                                                                                0
                                                                                Если соединение прервалось и восстановилось, в этот период может что-то продолбаться, так ведь?

                                                                                А в схеме, когда много-много подписчиков, каждый из них должен будет постоянно держать соединение с базой, чтобы не пропустить сообщение. Но соединение с базой в посгресе — это дорого. Вот это меня беспокоит
                                                                                  0
                                                                                  По поводу соединения — не задумывался пока.
                                                                                  Если не соединяться с базой — как с ней общаться? :) Как и с любым сервисом.
                                                                                    0
                                                                                    Ну в случае с классической очередью, можно хоть раз в час подсоединяться, проверять, нет ли чего нового, и отсоединяться. А тут прям привязан к соединению
                                                                                      0
                                                                                      Ну мы тут говорим про очереди или про pub/sub? :) Если всё что нужно — передать сообщение чтоб клиент его рано или поздно получил (например раз в час проверяет) — почему бы просто не записать в табличку, а потом оттуда прочитать?
                                                                                      Мы же используем этот механизм для реалтайма, то есть оповестить клиента настолько быстро насколько возможно. Это удобней чем городить огороды с rpc/http апи или ещё чем… с базой и так соединены оба.
                                                                                        0
                                                                                        Записать в табличку, а потом прочитать, плохо, как минимум, работает, когда читателей много, писатель о них не знает.
                                                                                          0
                                                                                          pg_notify можно сразу из тригерной процедуры делать для реалтайма, однако есть свои минусы
                                                                                            0
                                                                                            Яизначально хотел делать нотифи по сохранению строки в таблицу, но потом решил что будет проще просто делать ещё один SQL запрос из PHP, больше контроля и проще отладка для меня как для программиста (а совсем не DBA).
                                                                                        0

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


                                                                                        В последних постгресах есть API для декодирования WAL, и на гитхабе можно найти декодеры в json, protobufs и все такое. Возможно, это было бы более надежным решением, да и notify никакие не нужны — все само прилетит. Насколько я понимаю, отфильтровать только нужное можно тоже на уровне декодера.

                                                                                          +1
                                                                                          Ну мы тут не атомные ракеты программируем…
                                                                                            +1

                                                                                            Тогда вам нужны очереди. Если хочется странного оставаться в рамках Постгреса, то посмотрите в сторону PgQ, возможно, что это оно (сам не работал). А вообще, лучше не мучит мозг, а взять что-нибудьMQ: RabbitMQ, там, тысячи их.

                                                                                              –1
                                                                                              MQ все разные, но не знаю ни одной, которая позволяла бы воркеру может полность нового, может год назад поратовшего( штатно и быстро получить все пропущенные события с конкретного… момента
                                                                                                0

                                                                                                Идея с WAL decoder мне нравится тем, что можно получить функциональность RethinkDB, оставаясь в рамках постгреса.


                                                                                                Еще не пробовал на практике, но скоро собираюсь — на горизонте маячит подходящая задачка.

                                                                                            +1
                                                                                            Дорого устанавливать соединение. Держать установленное соединение разве дорого?
                                                                                  0
                                                                                  Оффтопик: Есть информация о том, что самая распространенная ORM для node.js (Sequelize.js, 400k загрузок в месяц) некорректно работает с postgres. Писать статью?
                                                                                    +1
                                                                                    Конечно! А если кратко, то в чем суть проблемы?
                                                                                      +2
                                                                                      В postgres есть hardcoded переменная, NAMEDATALEN = 64. Которая, среди прочего накладывает ограничение на длину идентификатора (названия таблиц, алиасов, полей) 64-1=63 символа. Если писать запросы руками, этого более чем достаточно. Однако ORM, при запросах с JOIN, для вложенных таблиц создает алиас вида ParenttablenameChildtablename, склеивая названия таблиц, запросто превышая этот предел длины.

                                                                                      Ни sequelize, ни postgres ошибок и предупреждений не выдает, и на сложных запросах спокойно и валидно возвращает не фронтенд некорректные данные. При этом в тестах (на более менее простых запросах) всё ок.

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

                                                                                      Ишью открыта с 2014 года и выглядит так, будто это не критическая проблема, а мелкая недоработка.

                                                                                      Простого решения нет. Мне пришлось пересобрать postgres из исходников, установив NAMEDATALEN = 1024. Но это плохое решение: во-первых, со слов разрабов, уже с NAMEDATALEN = 128 есть падение производительности postgres; во-вторых умеючи можно и 1024 превысить и не заметить.
                                                                                      +1

                                                                                      Напишите, а ишью создали?

                                                                                        0
                                                                                        Ишью открыто с 2014 года. Мило же?
                                                                                          +2
                                                                                          Это ишью надо в посгресе открывать имхо. Ну что такое 63 символа — смех один. Я тоже напарывался на это ограничение, и не в orm (генерили автоматом то ли хранимки, то ли юзеров, я забыл)
                                                                                            0
                                                                                            В маиллистах постреса поднимали эту тему некоторое время назад. Тогда увеличили с 32 до 64, отметив что дальнейшее увеличение дает performance degrade.
                                                                                            Я был бы рад даже не увеличению параметра, а хотя бы нормальной ошибке при превышении.
                                                                                              +2
                                                                                              я вот не понимаю причины использование в данном orm таких имён, можно ведь как в рельсах t1, t2, t3… алиасы использовать.
                                                                                                +2
                                                                                                Изначальных мотиваций две: вроде как проще дебажить сгенерированный SQL и проще парсить ответ (напрямую) в дерево. Я, было, хотел написать патч, но, честно говоря, просто зарылся коде и, за выделенное себе на это время — не осилил. Как-то там тяжело всё, целая экосистема и надо с умом подойти, чтобы сделать красиво.

                                                                                                Вообще, интересная ситуация. Я всё понимаю, open-source, никто ничего никому не должен, возьми и сделай, или хотя бы заранее читай все issue. Всё так. Но я всё равно как-то офигел, что такое возможно в 10k звездном проекте с 400 тысячами загрузок в месяц. Представляете, это была моя инициатива строить проект на node стеке, и тут, спустя два месяца работы, я понимаю, что в решении есть один «несущественный недостаток»: из базы приходят не все данные. Три дня и ночи искал у себя в коде баг. Потом нашел открытую ишью от 2014 года. Ещё три дня просто молчал и познавал дзен.
                                                                                      –10
                                                                                      В 2013 postgres был еще нормальной СУБД. Сейчас же надобавляли всякой ерунды ненужной. Пользоваться стало неудобно. Ресурсов стал есть мама не горюй. Стабильно раз в сутки приходится по cron его перегружать. Про репликации я вообще молчу. Как обычно, хорошее долго на рынке не держится. Даже подозрение закрадывается, а не конкурентов ли с проприетарной стороны баррикад это проделки. С нынешним впечатлением и речи не может быть о переходе с MySQL. Однозначное «нет».
                                                                                        +1
                                                                                        А можно конкретнее, какая ненужная ерунда вам мешает?
                                                                                          +6
                                                                                          Судя по комменту, автору мешал autovacuum. :D
                                                                                          Рестарт БД по крону — это 100% маркер, что человек делает что-то ну совсем не правильное.
                                                                                            –1
                                                                                            Вы меня пугаете. Где можно почитать как правильно делать запросы к БД, чтобы не нужно было рестартовать СУЬД по крону.
                                                                                              +3
                                                                                              Чет я не понимаю, к чему вы клоните. Зачем вам рестартить базу?
                                                                                          +2
                                                                                          Ресурсов стал есть мама не горюй
                                                                                          Как настроете так и будет жрать. На мой взгляд он экономнее MySQL.
                                                                                            +1

                                                                                            Вот не знаю даже, что вы с Постгресом такое сделали. Он из коробки настроен на очень экономное использование памяти, так, чтобы нормально работать на машинках класса Raspberry Pi и хуже. Ему специально надо «гайки ослаблять», чтобы разрешить использовать все ресурсы и раскрыть производительность по полной. Для старта возьмите PgTune, а уже потом есть мануалы по том, как его тюнить.

                                                                                            0
                                                                                            Спасибо за информацию! очень интересная статья!)
                                                                                              0

                                                                                              Да, в PostgreSQL мне понравились индексы по выражению.
                                                                                              Но есть и небольшая ложка дёгтя: если взять два поля, объединить их в range и построить индекс, то он будет срабатывать только когда в запросе тоже используются range.


                                                                                              То есть WHERE int8range(col1, col2) @> $value будет работать эффективно, а WHERE col1 <= $value AND $value < col2 — нет. Это не очень удобно при использовании ORM.

                                                                                                0
                                                                                                Так индекс по range, почему он должен использоваться для операций типа "<"?
                                                                                                  +1

                                                                                                  Потому что мне так хочется. Написано-то одно и то же. Вот пусть база сама догадается, что можно использовать индекс на range.

                                                                                                    +1
                                                                                                    Когда база начнет догадываться у нас работы не останется :)
                                                                                                      0
                                                                                                      Насколько я понимаю — не одно и то же. Вы уверены, что результаты будут одинаковы для разных типов col*?
                                                                                                    +1

                                                                                                    А это со всеми функциональными индексами так: ускоряются только запросы с условием с точно таким же выражением (и одним из поддерживаемых данным индексом операторов в условии ещё, сверяйтесь в документации — btree и gist ускоряют разные запросы!).

                                                                                                    –2
                                                                                                    SELECT
                                                                                                      count(*) FILTER (WHERE age > 20) AS old,
                                                                                                      count(*) FILTER (WHERE age <= 20) AS young
                                                                                                    
                                                                                                    FROM users;

                                                                                                    И чем это принципиально лучше такого?


                                                                                                    SELECT 
                                                                                                      SUM(IF(age>20,1,0)) AS old,
                                                                                                      SUM(IF(age<=20,1,0)) AS young 
                                                                                                    FROM users;
                                                                                                      +3
                                                                                                      Читабельностью
                                                                                                        –2

                                                                                                        Не смешно.
                                                                                                        Брюнетка/блондинка…


                                                                                                        Ещё есть плюсы?
                                                                                                        Ну там используются partial индексы, например?

                                                                                                          +1

                                                                                                          К сожалению, нет, ни в том, ни в другом варианте:


                                                                                                          1. count и filter
                                                                                                            EXPLAIN ANALYZE SELECT count(*) FILTER (WHERE age < 18) AS young FROM people;
                                                                                                               ->  Seq Scan on people  (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.060..646.134 rows=11000000 loops=1)
                                                                                                          2. sum с условием. В PostgreSQL нет IF (function if(boolean, integer, integer) does not exist), поэтому вместо этого используется CASE:


                                                                                                            EXPLAIN ANALYZE SELECT (SUM(CASE WHEN age < 18 THEN 1 ELSE 0 END)) AS young FROM people;
                                                                                                               ->  Seq Scan on people  (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.042..674.906 rows=11000000 loops=1)
                                                                                                            

                                                                                                          3. Однако если WHERE присобачить уже в конце выражения, после FROM, только тогда используются:
                                                                                                            EXPLAIN ANALYZE SELECT count(*) FROM people WHERE age < 18;
                                                                                                               ->  Index Only Scan using young_people on people  (cost=0.43..55783.71 rows=1952507 width=0) (actual time=0.043..153.447 rows=1925242 loops=1)

                                                                                                          Таблица для проверки:


                                                                                                          CREATE TABLE people (id serial, name varchar, age int, primary key (id));
                                                                                                          INSERT INTO people (name, age) SELECT md5(random()::text)::varchar AS name, (random() * 100)::int AS age FROM generate_series(1, 1000000); -- достаточно большая таблица, чтобы планировщик захотел заморочиться с индексами
                                                                                                          CREATE INDEX young_people ON people (age) WHERE age < 18;
                                                                                                          VACUUM ANALYZE people;
                                                                                                            0

                                                                                                            На самом деле неправильно вас понял (перепутал покрывающие с частичными индексами), но ответа это не меняет.


                                                                                                            Используйте вот такую конструкцию, если хотите оба значения и использовать индекс (и то, только при условии, что оба фильтра отсекают большое количество записей):


                                                                                                            SELECT (SELECT count(*) FROM people WHERE age < 18) AS young, (SELECT count(*) FROM people WHERE age >= 18) AS too_old;

                                                                                                                                                                                 QUERY PLAN                                                                     
                                                                                                            ----------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                             Result  (cost=29282.67..29282.68 rows=1 width=16) (actual time=160.145..160.145 rows=1 loops=1)
                                                                                                               InitPlan 1 (returns $0)
                                                                                                                 ->  Aggregate  (cost=5369.82..5369.84 rows=1 width=8) (actual time=23.040..23.040 rows=1 loops=1)
                                                                                                                       ->  Index Only Scan using people_age on people  (cost=0.42..4936.65 rows=173270 width=0) (actual time=0.016..15.298 rows=175201 loops=1)
                                                                                                                             Index Cond: (age < 18)
                                                                                                                             Heap Fetches: 0
                                                                                                               InitPlan 2 (returns $1)
                                                                                                                 ->  Aggregate  (cost=23912.83..23912.83 rows=1 width=8) (actual time=137.100..137.100 rows=1 loops=1)
                                                                                                                       ->  Seq Scan on people people_1  (cost=0.00..21846.00 rows=826730 width=0) (actual time=0.010..100.024 rows=824799 loops=1)
                                                                                                                             Filter: (age >= 18)
                                                                                                                             Rows Removed by Filter: 175201
                                                                                                             Planning time: 0.065 ms
                                                                                                             Execution time: 160.170 ms

                                                                                                            Обратите внимание, что во втором случае планировщик всё равно предпочёл полное сканирование, потому что колонок в таблице мало, а второе условие отбирает бо́льшую часть записей в таблице и профита лезть в индекс нет.

                                                                                                        0

                                                                                                        1) второй вариант это всё же хак — нам нужно количество, а мы считаем сумму нулей и единичек, да ещё пользуясь нестандартной функцией, а оконные функции, в том числе агрегирующие часть стандарта, насколько я знаю


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

                                                                                                      Only users with full accounts can post comments. Log in, please.