Conditional ordering operators

Some times ago I've written a script which creates two operators:

@< - ascending ordering
@> - descending ordering

(here is the script conditional_ordering.sql)

It allows you to replace code like this

if <condition1> then
  for
      select <fields>
      from <tables>
      where <restrictions>
      order by
          field1 desc,
          field2
  loop
      <actions>
  end loop;
elsif <condition2> then
  for
      select <fields>
      from <tables>
      where <restrictions>
      order by
          field3,
          field1 desc,
          field2 desc
  loop
      <actions>
  end loop;
else
  for
      select <fields>
      from <tables>
      where <restrictions>
      order by
          field4
  loop
      <actions>
  end loop;
end if;

that way

for
  select <fields>
  from <tables>
  where <restrictions>
  order by
      case when <condition1> then
          @>field1
          @<field2
      when <condition2> then
          @<field3
          @>field1
          @>field2
      else
          @<field4
      end
loop
  <actions>
end loop;

It looks better, doesn't it?

Also it provides Oracle like OVER PARTITION effect

select * from (
  values
  (1.2, '2007-11-23 12:00'::timestamp, true),
  (1.4, '2007-11-23 12:00'::timestamp, true),
  (1.2, '2007-11-23 12:00'::timestamp, false),
  (1.4, '2007-01-23 12:00'::timestamp, false),
  (3.5, '2007-08-31 13:35'::timestamp, false)
) _
order by
  @<column1 ||
  case
  when column1 = 1.2 then @<column3
  when column1 = 1.4 then @>column3
  else
      @>column2
      @<column3
  end;

column1 |       column2       | column3
---------+---------------------+---------
   1.2 | 2007-11-23 12:00:00 | f
   1.2 | 2007-11-23 12:00:00 | t
   1.4 | 2007-11-23 12:00:00 | t
   1.4 | 2007-01-23 12:00:00 | f
   3.5 | 2007-08-31 13:35:00 | f
(5 rows)

Note that rows 1-2 and 3-4 have opposite order in third column.

p.s. Unfortunately I haven't manage yet with text fields because of
localization.

Here is the script conditional_ordering.sql

Условное упорядочивание по произвольному набору полей

Вспомнил, вот, свою старенькую наработку и решил опубликовать:

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

Для решения этой проблемы я написал пару операторов:

@< - сортировка в прямом порядке
@> - сортировка в обратном порядке

(скрипт тут conditional_ordering.sql)

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

if <condition1> then
   for
       select <fields>
       from <tables>
       where <restrictions>
       order by
           field1 desc,
           field2
   loop
       <actions>
   end loop;
elsif <condition2> then
   for
       select <fields>
       from <tables>
       where <restrictions>
       order by
           field3,
           field1 desc,
           field2 desc
   loop
       <actions>
   end loop;
else
   for
       select <fields>
       from <tables>
       where <restrictions>
       order by
           field4
   loop
       <actions>
   end loop;
end if;

Конечно это можно частично обойти за счёт использования курсоров или динамического SQL, но, сами понимаете, в первом случае избыточность в запросах никуда не денется, а во втором появятся проблемы со скоростью. Теперь та же логика, только с использованием новых операторов:

for
   select <fields>
   from <tables>
   where <restrictions>
   order by
       case when <condition1> then
           @>field1
           @<field2
       when <condition2> then
           @<field3
           @>field1
           @>field2
       else
           @<field4
       end
loop
   <actions>
end loop;

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

select * from (
   values
   (1.2, '2007-11-23 12:00'::timestamp, true),
   (1.4, '2007-11-23 12:00'::timestamp, true),
   (1.2, '2007-11-23 12:00'::timestamp, false),
   (1.4, '2007-01-23 12:00'::timestamp, false),
   (3.5, '2007-08-31 13:35'::timestamp, false)
) _
order by
   @<column1 ||
   case
   when column1 = 1.2 then @<column3
   when column1 = 1.4 then @>column3
   else
       @>column2
       @<column3
   end;

 column1 |       column2       | column3
---------+---------------------+---------
    1.2 | 2007-11-23 12:00:00 | f
    1.2 | 2007-11-23 12:00:00 | t
    1.4 | 2007-11-23 12:00:00 | t
    1.4 | 2007-01-23 12:00:00 | f
    3.5 | 2007-08-31 13:35:00 | f
(5 rows)

Обратите внимание на то, что строки 1-2 и 3-4 имеют разный порядок в третьей колонке.

p.s. К сожалению операторы пока не работают с текстовыми полями, т.к. мне пока не удалось победить локализацию, да и цели такой пока не было.

Скрипт тут conditional_ordering.sql

Как получить элементы enum?

Перевод How to determine what elements are in your enum с Postgres OnLine Journal

Этот вопрос недавно задавали в рассылке для новичков, где Tom Lane дал на него ответ. Если вы используете 8.3 и ENUM, то вам скорее всего это тоже будет интересно. Так что мы посчитали нужным опубликовать его:

Вопрос: Дан ENUM, Есть ли такой запрос, который выведет список всех элементов, допустимых этим ENUM-ом?

Ответ: Да.

CREATE TYPE myenum as enum ('red','green','blue');
SELECT enumlabel 
   FROM pg_enum 
   WHERE enumtypid = 'myenum'::regtype 
   ORDER BY oid;

 enumlabel
-----------
 red
 green
 blue
(3 rows)

http://archives.postgresql.org/pgsql-novice/2008-12/msg00043.php
http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html

Почему делается перепроверка условий (Recheck Cond)?

Перевод Explain: Why do I have to recheck my condition? с Command Prompt Inc., Joshua Drake's blog

Если у вас возник вопрос по PostgreSQL, первым местом, где надо искать ответ должна быть документация по PostgreSQL. Недавно я пересматривал документацию по EXPLAIN, освежая в памяти нюансы по тюнингу запросов и наткнулся на такую вот штуку:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
         Index Cond: (unique1 < 100)

Что сказано в документации по этому поводу:

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

В кратце, мы проходим по индексу и выясняем какие записи соответствуют индексному условию (unique1 < 100). Единственное что я не понял и не увидел в документации, это то, почему надо делать перепроверку в верхнем узле (Recheck Cond: (unique1 < 100)), если индекс уже сказал мне, какие записи соответствуют условию.

После небольшого расследования и помощи Neil Conway я нашел причину. Bitmap сканирование является не чётким (loosy) и с ростом количества записей, полученных этим сканированием, PostgreSQL перешел из режима "сопоставления записей" в режим "сопоставления страниц". А так как страницы могут содержать несколько записей, необходимо делать перепроверку (Bitmap Heap scan).

В ожидании 8.4 - Карты видимости

Перевод Waiting for 8.4 - Visibility maps с select * from depesz;

(от автора) О-да, детка! ;)

Да. Только ради этого патча будет смысл обновиться до 8.4.

Его принял Heikki Linnakangas 3-го декабря. Сообщение:

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

Пассивная сборка мусора (lazy vacuum) использует карты видимости для того, чтобы 
пропускать страницы, не требующие очистки. Сборка мусора также ответственна за 
установку битов видимости. В будущем это даёт возможность реализации index-only 
сканирований, но в данный момент нельзя гарантировать, что карты видимости всегда 
будут актуальны.

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

Ранее был ещё один патч, позволяющий autovacuum-у использовать эти возможности.

Даже сейчас 8.4 можно назвать великим. В нём реализовано множество новых возможностей. Кто-то назовёт CTE более важным. Другие скажут, что это локаль на уровне базы.

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

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

Что же мы получили? В кратце - сборка мусора стала быстрее. Возможно намного.

Давайте посмотрим.

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

Теперь создадим 4 тестовых таблицы:

CREATE TABLE test_1 (i INT4);
CREATE TABLE test_2 (i INT4);
CREATE TABLE test_3 (i INT4);
CREATE TABLE test_4 (i INT4);

добавим данных:

INSERT INTO test_1 SELECT generate_series(1, 100000000);
INSERT INTO test_2 SELECT generate_series(1, 100000000);
INSERT INTO test_3 SELECT generate_series(1, 100000000);
INSERT INTO test_4 SELECT generate_series(1, 100000000);

(знаю, что это не много, но я на ноуте и не хочу ждать всю ночь :)

Теперь обновим таблицы:

UPDATE test_2 SET i = i + 1 WHERE i < 10000000;
UPDATE test_3 SET i = i + 1 WHERE i < 50000000;
UPDATE test_4 SET i = i + 1 WHERE i < 90000000;

Для проверки нового функционала надо запустить vacuum несколько раз, т.к. каждый vacuum использует информацию полученную предыдущим. Т.ч. я сделал:

VACUUM test_1;
VACUUM test_2;
VACUUM test_3;
VACUUM test_4;

после INSERT-ов и после UPDATE-ов. Для большей информативности я ещё раз сделал UPDATE и после него ещё раз vacuum.

Результат:

		Visibility maps available		Change
		No	Yes
Vacuum #1 post-insert	test_1	233.34 s	310.53 s	+33.1 %
	test_2	243.45 s	262.20 s	+7.7 %
	test_3	237.86 s	260.26 s	+9.4 %
	test_4	198.72 s	200.60 s	+0.9 %
Vacuum #2 post-update #1	test_1	96.71 s	0.38 s	-99.6 %
	test_2	150.91 s	59.81 s	-60.4 %
	test_3	283.22 s	234.06 s	-17.4 %
	test_4	418.41 s	503.25 s	+20.3 %
Vacuum #3 post-update #2	test_1	98.11 s	0.54 s	-99.4 %
	test_2	142.92 s	27.10 s	-81.0 %
	test_3	283.91 s	297.43 s	+4.8 %
	test_4	416.35 s	507.77 s	+22.0 %

Что получается? На самом деле PostgreSQL с картами видимости отработал медленнее в ситуациях с большим кол-вом "новых" записей, не зависимо от того были ли они добавлены или изменены. Но если ваш autovacuum настроен правильно - этого не случится. Очевидно ему желательно видеть не более ~10% изменений строк, и тогда производительность просто взлетит.

В дополнение к этому, если ваши таблицы (не зависимо от размера) в основном неизменны, то их vacuum будет практически моментален - очень полезное свойство.

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

Хочу заострить внимание на выдержке из сообщения патча:

В будущем это даёт возможность реализации index-only 
сканирований...

Это очень многообещающе. Другие СУБД (включая открытые) реализуют такое через так называемые covering indexes (когда запрос оперирует только полями содержащимися в индексе, и не приходится лезть в таблицу), но PostgreSQL всё ещё удаётся их избегать. Стремления очевидны, и карты видимости - 1-й шаг к окончательной победе :)

NULL и NOT IN()

Перевод NULLs vs. NOT IN() с select * from depesz;

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

Очень простой запрос

select * from table where id not in (select field from other_table)

не возвращал ни одной строки, несмотря на то, что точно существовали такие id, которых не было в other_table.field. Как это?

Для начала смоделируем ситуацию:

CREATE TABLE objects (id INT4 PRIMARY KEY);
CREATE TABLE secondary (object_id INT4);
INSERT INTO objects (id) VALUES (1), (2), (3);
INSERT INTO secondary (object_id) VALUES (NULL), (2), (4);

Данные:

# select * from objects ;
 id
----
  1
  2
  3
(3 rows)

# select * from secondary ;
 object_id
-----------
    [null]
         2
         4
(3 rows)

Ок. Первым делом проверим:

# SELECT * FROM objects WHERE id IN (SELECT object_id FROM secondary);
 id
----
  2
(1 row)

Таким образом, опираясь на то, что у нас есть 3-и id объектов (1, 2, 3) и IN работает только для 2-ки, NOT IN должен вернуть 1 и 3. Правильно? Не правильно:

# SELECT * FROM objects WHERE id NOT IN (SELECT object_id FROM secondary);
 id
----
(0 rows)

Чё за... Почему?

Проверим результат id NOT IN для каждого id:

# SELECT id, id NOT IN (SELECT object_id FROM secondary) FROM objects;
 id | ?column?
----+----------
  1 | [null]
  2 | f
  3 | [null]
(3 rows)

Хм... но почему?

Разберём случай с id = 2.

Сравнение с 2 во второй таблице - id (2) равен 2, т.ч. можем утверждать, что NOT IN вернёт false. Дальнейшая проверка не требуется.
Сравнение с 4 во второй таблице - id (2) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Получается, что для id = 2 у нас будет false, потому что во второй таблице есть строка с object_id = 2.

Но что будет при сравнении с id = 3?

Сравнение с 2 во второй таблице - id (3) не равен 2, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с 4 во второй таблице - id (3) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Для двух строк NOT IN сработало, но для одной - мы не уверены. NULL - "неизвестно", т.ч. оно может оказаться и тройкой (3). Мы не знаем. И по этому NOT IN должен вернуть NULL.

Если посмотреть ближе, то можно увидеть, что технически не возможно получить true в данной ситуации, пока во второй таблице есть значения NULL!

Что делать?

Есть очень простое решение:

# SELECT id, id NOT IN (SELECT object_id FROM secondary WHERE object_id IS NOT NULL) FROM objects;
 id | ?column?
----+----------
  1 | t
  2 | f
  3 | t
(3 rows)

Которое возвращает строки с id = 1 и id = 3.