В ожидании 9.1 - CREATE TABLE IF NOT EXISTS

Перевод Waiting for 9.1 – CREATE TABLE IF NOT EXISTS с select * from depesz;

25 июля Robert Haas применил патч, добавляющий CREATE IF NOT EXISTS для таблиц.

CREATE TABLE IF NOT EXISTS.

Reviewed by Bernd Helmle.

Пример тривиален:

$ create table if not exists tesit (x text);
CREATE TABLE

$ create table if not exists tesit (x text);
NOTICE: relation "tesit" already exists, skipping
CREATE TABLE

Как можно видеть ошибки нет - просто дружелюбное замечание.

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

Небольшое предпочтение в sql-mode Emacs

В общем чуток кастомизации sql-mode. Я привык в SQL к отступам в 4-е пробела и мне нравится контроль над пробельными символами (мой пунктик).

В управляющем файле ставим загрузку конфигурации для разных sql:

~/.emacs:
(load "~/.emacs.d/sql.el")

В конфигурации sql:

~/.emacs.d/sql.el:
;; Minor modes
(add-hook 'sql-mode-hook 'whitespace-mode)

;; 4 spaces instead of tab
(add-hook 'sql-mode-hook
'(lambda ()
(setq indent-tabs-mode nil
tab-width 4
indent-line-function 'insert-tab)
(define-key sql-mode-map (kbd "C-j")
'(lambda()
(interactive)
(delete-horizontal-space t)
(newline)
(indent-relative-maybe)))))

Всё, теперь меня ничего не смущает, гут :)

Правка от 2010-03-26
Установил функцию общего отступа в обычную табуляцию:

indent-line-function 'insert-tab

И переопределил "C-j" с использованием относительного отступа где надо:

(define-key sql-mode-map (kbd "C-j")
'(lambda()
(interactive)
(delete-horizontal-space t)
(newline)
(indent-relative-maybe)))))

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

В ожидании 8.4 - pl/* srf функции в выборках

Перевод Waiting for 8.4 - pl/* srf functions in selects с select * from depesz;

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

Комментарий к патчу:

Расширяет ExecMakeFunctionResult() поддержкой set-returning
функций, возвращающих значения с использованием tuplestore вместо механизма
значение-за-вызов. Проведён рефакторинг некоторых вещей, устраняющий дублирование 
кода с помощью nodeFunctionscan.c. Это не обсуждаемая часть моего патча для перевода
SQL функций на возврат tuplestore. На данный момент SQL функции всё ещё ведут себя 
по старому. Однако, теперь возможно использовать PL SRF функции в целевом списке 
полей.

Что это даёт. Как вы возможно знаете, нельзя сделать join таблицы с функцией. Если функция возвращает >1 строки (или >1 колонки), не возможно её вызвать, передав ей в качестве аргумента поле из таблицы, используемой в запросе.

Я думаю описание немного сложновато, так что сразу перейдём к примеру.

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

CREATE OR REPLACE FUNCTION test(
    IN from_i INT4,
    IN to_i INT4,
    OUT numerical INT4,
    OUT textual TEXT
) RETURNS setof record as $$
DECLARE
i INT4;
BEGIN
    for i in from_i .. to_i LOOP
        numerical := i;
        textual := 'i = ' || i;
        RETURN next;
    END loop;
    RETURN;
END;
$$ language plpgsql;

Эта простая функция делает следующее:

# select * from test(2,5);
 numerical | textual
-----------+---------
         2 | i = 2
         3 | i = 3
         4 | i = 4
         5 | i = 5
(4 rows)

Теперь, допустим, мы хотим (по какой-то причине) получить такие строки для каждой пары следующего выражения:

# select 1 as from_i, i as to_i from generate_series(1,3) i;
 from_i | to_i
--------+------
      1 |    1
      1 |    2
      1 |    3
(3 rows)

По большому счёту это затруднительно. Я не могу сделать join generate_series и моей функции. Если бы она была на SQL, то можно было бы сделать так

select i, test(...) from

но она на pl/pgsql. Конечно, я бы мог написать для неё SQL обёртку, но это совсем не здорово. К счастью, с новым патчем, pl/pgsql (и другие pl/*) функции смогут вызываться так же как и просто SQL функции:

# select i, test(1, i) from generate_series(1,3) i;
 i |    test
---+-------------
 1 | (1,"i = 1")
 2 | (1,"i = 1")
 2 | (2,"i = 2")
 3 | (1,"i = 1")
 3 | (2,"i = 2")
 3 | (3,"i = 3")
(6 rows)

Но что, если надо получить колонки отдельно?

Это можно сделать так:

select i, (test(1, i)).numerical, (test(1,i)).textual from generate_series(1,3) i;

Но в этом случае test() будет вызываться дважды для каждой строки, что будет проблематично, если там будет что-нибудь посложнее, чем просто цикл.

К счастью это можно также сделать простым подзапросом:

# select i, (test).numerical, (test).textual
    from (select i, test(1, i) from generate_series(1,3) i) x;
 i | numerical | textual
---+-----------+---------
 1 |         1 | i = 1
 2 |         1 | i = 1
 2 |         2 | i = 2
 3 |         1 | i = 1
 3 |         2 | i = 2
 3 |         3 | i = 3
(6 rows)

Превосходно. Ещё одна проблема позади.

*ДОПОЛНЕНИЕ*

(после напоминания от David Fetter)

CTE (основные табличные выражения) настолько новы, что я просто о них забыл, вот они:

with source as (
   select i, test(1, i) from generate_series(1,3) i
)
select i, (test).numerical, (test).textual from source;

explain analyze

для CTE:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 CTE Scan on source  (cost=262.50..282.50 rows=1000 width=36) (actual time=2.331..2.534 rows=6 loops=1)
   InitPlan
     ->  Function Scan on generate_series i  (cost=0.00..262.50 rows=1000 width=4) (actual time=2.315..2.488 rows=6 loops=1)
 Total runtime: 2.646 ms
(4 rows)

и для подзапроса:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Subquery Scan x  (cost=0.00..272.50 rows=1000 width=36) (actual time=0.236..0.423 rows=6 loops=1)
   ->  Function Scan on generate_series i  (cost=0.00..262.50 rows=1000 width=4) (actual time=0.229..0.395 rows=6 loops=1)
 Total runtime: 0.477 ms
(3 rows)

От автора перевода:

Немного не понятно, почему в первом случае Function Scan занял больше времени. Возможно это было связано с разной нагрузкой на сервер во время обоих тестов. И я думаю, в данном случае смотреть надо на estimation, а не на actual time. Буду рад почитать ваши мысли по этому поводу в комментариях.

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

select i, (test(1, i)).* from generate_series(1,3) i;

Или я ошибаюсь?

*ДОПОЛНЕНИЕ*

Да, я оказался прав

# select i, (test(1, i)).* from generate_series(1,3) i;
i | numerical | textual
—+———–+———
1 | 1 | i = 1
2 | 1 | i = 1
2 | 2 | i = 2
3 | 1 | i = 1
3 | 2 | i = 2
3 | 3 | i = 3
(6 rows)

В ожидании 8.4 - RETURNING в подзапросах

Перевод Waiting for 8.4 - sql-wrappable RETURNING с select * from depesz;

В PostgreSQL 8.2 было добавлено выражение RETURNING для INSERT/UPDATE/DELETE запросов. К сожалению, оно не могло быть использовано как источник строк для всего в SQL.

insert into table_backup delete from table where ... returning *;

В прочем, это и сейчас не возможно, но был сделан один шаг в правильном направлении, благодаря патчу от Tom Lane 31го Октября:

Позволяет SQL-функциям возвращать вывод INSERT/UPDATE/DELETE RETURNING выражений, а не только SELECT как прежде.

Дополнительный эффект этого патча таков, что когда возвращающая множество SQL функция используется в FROM, производительность увеличивается за счёт того, что вывод накапливается в tuplestore внутри функции, в отличие от менее эффективного значение-за-вызов механизма.

Как это работает? Всё просто. Начнём с тестовой таблицы:

# create table test (i int4);
CREATE TABLE

С тестовым контентом:

# insert into test select generate_series(1, 10);
INSERT 0 10

Теперь создадим свою SQL функцию удаляющую строки:

CREATE function delete_from_test_returning(INT4) RETURNS setof test as $$
    DELETE FROM test WHERE i <= $1 returning *
$$ language sql;

Как видно, функция очень проста.

Теперь используем её для бэкапа удаленных строк:

# create table delete_backup as select * from delete_from_test_returning(3);
SELECT

И проверим содержание обеих таблиц:

# select * from test;
 i
----
  4
  5
  6
  7
  8
  9
 10

(7 rows)

# select * from delete_backup;
 i
---
 1
 2
 3

(3 rows)

Конечно, я мог бы сделать это раньше в pl/pgsql функции, которая бы пробегалась по всем возвращаемым строкам, но в данном случае это определённо будет быстрее.

В ожидании 8.4 - Общие табличные выражения (WITH-запросы)

Перевод Waiting for 8.4 - Common Table Expressions (WITH queries) с select * from depesz;

Общее табличное выражение (от англ. Common Table Expressions, CTE) - временный именованный набор данных, полученный из простого запроса и определённый в области действия операций SELECT, INSERT, UPDATE, или DELETE.

4 сентября Tom Lane применил очередной замечательный патч. В этот раз он довольно весомый, т.ч. даже после принятия в нём остаются кое-какие недоделки. Понадобятся дополнительные патчи для того, чтобы реализовать полный функционал, но сам факт того, что он был принят означает его появление в 8.4.

Что же он делает?

Сначала посмотрим описание патча:

Реализует соответствующее SQL стандарту выражение WITH, включая WITH RECURSIVE.

Имеются некоторые не реализованные аспекты: рекурсивные запросы должны использовать
UNION ALL (использование UNION должно тоже позволяться) и у нас нет выражений SEARCH и CYCLE.

Они могут быть сделаны или нет к 8.4, но даже без них это очень полезная возможность.

Так же имеется пара маленьких неопределённостей и ухищрений о которых я упоминал в 
pgsql-hackers. Но давайте примем патч сейчас для того, чтобы привлечь других разработчиков.
 
Yoshiyuki Asaba, с огромной помощью Taysuo Ishii и Tom Lane.

Описание действительно выглядит интересно, ссылается на SQL стандарт, но что мы реально получаем?

Пример прямо из новой документации:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Не сложно заметить, что запросы WITH не что иное как инлайновые view или лучше временные таблицы, но существующие только во время выполнения запроса.

Что это даёт?

Давайте проверим:

# create table orders (region int4, product int4, quantity int4, amount int4);
CREATE TABLE

# insert into orders (region, product, quantity, amount)
    select random() * 1000, random() * 5000, 1 + random() * 20, 1 + random() * 1000
        from generate_series(1,10000);

INSERT 0 10000

Во первых, попробуем запрос из документации (немного изменённый):

# explain analyze
>> WITH regional_sales AS (
>>         SELECT region, SUM(amount) AS total_sales
>>         FROM orders
>>         GROUP BY region
>>      ), top_regions AS (
>>         SELECT region
>>         FROM regional_sales
>>         WHERE total_sales > (SELECT 2 * avg(total_sales) FROM regional_sales)
>>      )
>> SELECT region,
>>        product,
>>        SUM(quantity) AS product_units,
>>        SUM(amount) AS product_sales
>> FROM orders
>> WHERE region IN (SELECT region FROM top_regions)
>> GROUP BY region, product;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=509.55..539.52 rows=1998 width=16) (actual time=71.882..72.254 rows=221 loops=1)
   InitPlan
     ->  HashAggregate  (cost=205.00..217.51 rows=1001 width=8) (actual time=31.532..33.234 rows=1001 loops=1)
           ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=8) (actual time=0.005..13.888 rows=10000 loops=1)
     ->  CTE Scan on regional_sales  (cost=22.54..47.56 rows=334 width=4) (actual time=39.250..39.719 rows=12 loops=1)
           Filter: ((total_sales)::numeric > $1)
           InitPlan
             ->  Aggregate  (cost=22.52..22.54 rows=1 width=8) (actual time=7.666..7.667 rows=1 loops=1)
                   ->  CTE Scan on regional_sales  (cost=0.00..20.02 rows=1001 width=8) (actual time=0.002..4.786 rows=1001 loops=1)
   ->  Hash Join  (cost=12.02..224.50 rows=1998 width=16) (actual time=40.069..71.422 rows=221 loops=1)
         Hash Cond: (public.orders.region = top_regions.region)
         ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=16) (actual time=0.011..16.861 rows=10000 loops=1)
         ->  Hash  (cost=9.52..9.52 rows=200 width=4) (actual time=39.825..39.825 rows=12 loops=1)
               ->  HashAggregate  (cost=7.51..9.52 rows=200 width=4) (actual time=39.785..39.805 rows=12 loops=1)
                     ->  CTE Scan on top_regions  (cost=0.00..6.68 rows=334 width=4) (actual time=39.254..39.762 rows=12 loops=1)
 Total runtime: 72.674 ms

(16 rows)

Как вы можете видеть я изменил определение "top_regions" - вместо 10% продаж я определил лучшие регионы как более чем в два раза превысившие средние продажи.

Теперь попробуем переписать этот запрос без использования WITH:

# explain analyze
>> SELECT region,
>>        product,
>>        SUM(quantity) AS product_units,
>>        SUM(amount) AS product_sales
>> FROM orders
>> WHERE region IN (
>>     SELECT region
>>     FROM orders
>>     group by region
>>     having sum(amount) > 2 * (
>>         select avg(sum) from (
>>             Select sum(amount) from orders group by region
>>         ) as x
>>     )
>> )
>> GROUP BY region, product;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=710.04..740.01 rows=1998 width=16) (actual time=130.271..130.644 rows=221 loops=1)
   ->  Hash Join  (cost=477.58..690.06 rows=1998 width=16) (actual time=85.452..129.649 rows=221 loops=1)
         Hash Cond: (public.orders.region = public.orders.region)
         ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=16) (actual time=0.011..16.427 rows=10000 loops=1)
         ->  Hash  (cost=465.07..465.07 rows=1001 width=4) (actual time=85.157..85.157 rows=12 loops=1)
               ->  HashAggregate  (cost=435.04..455.06 rows=1001 width=8) (actual time=83.615..85.127 rows=12 loops=1)
                     Filter: ((sum(public.orders.amount))::numeric > (2::numeric * $0))
                     InitPlan
                       ->  Aggregate  (cost=230.03..230.04 rows=1 width=8) (actual time=47.372..47.374 rows=1 loops=1)
                             ->  HashAggregate  (cost=205.00..217.51 rows=1001 width=8) (actual time=41.329..43.404 rows=1001 loops=1)
                                   ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=8) (actual time=0.007..20.318 rows=10000 loops=1)
                     ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=8) (actual time=0.005..15.737 rows=10000 loops=1)
 Total runtime: 131.050 ms
(13 rows)

Как видно - это медленней. Причина очень проста. "Старый" запрос вынужден сканировать таблицу 3 раза.

Новый это делает только 2 раза.

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

Но это не всё. Также существует специальная форма WITH запросов, которые могут дать эффект, не достижимый прежде. Это WITH RECURSIVE.

Представим простейшую древовидную структуру:

create table tree (id serial primary key, parent_id int4 references tree (id));
insert into tree (parent_id) values (NULL);
insert into tree (parent_id)
    select case when random() < 0.95 then floor(1 + random() * currval('tree_id_seq')) else NULL end
        from generate_series(1,1000) i;

Тут создаётся "лес" (не просто "дерево", т.к. имеют место несколько корневых элементов), который имеет (с моими случайными данными):

- 1001 элемент
- 56 корневых узлов
- 28 корневых узлов содержащих дочерние элементы
- самый длинный путь содержит 16 узлов: 1 -> 2 -> 3 -> 7 -> 12 -> 15 -> 39 -> 52 -> 61 -> 107 -> 123 -> 194 -> 466 -> 493 -> 810 -> 890

Традиционно, если понадобится вывести всех родителей узла 890, потребуется написать цикл делающий запросы до тех пор, пока не будет получена строка, где "parent_id IS NULL".

Но сейчас мы можем сделать следующее:

WITH RECURSIVE struct AS (
    SELECT t.* FROM tree t WHERE id = 890
UNION ALL
    SELECT t.* FROM tree t, struct s WHERE t.id = s.parent_id
)
SELECT * FROM struct;

Что работает действительно здорово:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on struct  (cost=402.18..404.20 rows=101 width=8) (actual time=0.037..69.311 rows=16 loops=1)
   InitPlan
     ->  Recursive Union  (cost=0.00..402.18 rows=101 width=8) (actual time=0.030..69.222 rows=16 loops=1)
           ->  Index Scan using tree_pkey on tree t  (cost=0.00..8.27 rows=1 width=8) (actual time=0.024..0.029 rows=1 loops=1)
                 Index Cond: (id = 890)
           ->  Hash Join  (cost=0.33..39.19 rows=10 width=8) (actual time=2.165..4.313 rows=1 loops=16)
                 Hash Cond: (t.id = s.parent_id)
                 ->  Seq Scan on tree t  (cost=0.00..35.01 rows=1001 width=8) (actual time=0.005..2.434 rows=1001 loops=15)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4) (actual time=0.012..0.012 rows=1 loops=16)
                       ->  WorkTable Scan on struct s  (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.005 rows=1 loops=16)
 Total runtime: 69.445 ms
(11 rows)

Вас может смутить наличие "Seq Scan on tree…loops=15", но не стоит беспокоиться. Это так из-за очень малого количества строк в таблице.

После добавления дополнительных 50000 строк получаем:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on struct  (cost=840.76..842.78 rows=101 width=8) (actual time=0.039..0.672 rows=16 loops=1)
   InitPlan
     ->  Recursive Union  (cost=0.00..840.76 rows=101 width=8) (actual time=0.032..0.591 rows=16 loops=1)
           ->  Index Scan using tree_pkey on tree t  (cost=0.00..8.27 rows=1 width=8) (actual time=0.025..0.029 rows=1 loops=1)
                 Index Cond: (id = 890)
           ->  Nested Loop  (cost=0.00..83.05 rows=10 width=8) (actual time=0.018..0.027 rows=1 loops=16)
                 ->  WorkTable Scan on struct s  (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.004 rows=1 loops=16)
                 ->  Index Scan using tree_pkey on tree t  (cost=0.00..8.27 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=16)
                       Index Cond: (t.id = s.parent_id)
 Total runtime: 0.799 ms
(10 rows)

Что выглядит превосходно.

Как я упоминал ранее, остаются вещи нуждающиеся в доработке. Но даже сейчас WITH запросы выглядят великолепно.

Space-time: cube and btree_gist use

Hello

I want to share my recent experience with geometric data processing and optimization in postgresql. The solution I'm going to describe has produced a good practical effect.

Let's assume we have a table for geo data logging geodata_log containing objects location column obj_point (point) and capture time obj_created (timestamp). Also there's a query that is used to obtain the latest object from specified square very often:

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875)) 
ORDER BY
    obj_created 
LIMIT 1;

Adding data into the table is rather intencive so we can ensure that at least one record has been added for the last 3 months. Accordingly we can supply the query with additional condition on capture time:

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))
    AND obj_created > '2008-07-04'
ORDER BY
    obj_created
LIMIT 1;

It's clear that case without indexes doesn't worth our attention. So let's create an index on location and capture time:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

And we've got an error:

ERROR: data type timestamp with time zone has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

Similar message we will see if we try to create btree index. The thing is that int4, timestamp and other common types are supported by btree oposite to geometric types that are supported by gist.

Let's create two indexes, first on location:

CREATE INDEX i_geodata_log__point
 ON geodata_log
 USING gist
 (box(obj_point, obj_point));

Query plan is:

Limit  (cost=256.03..256.04 rows=1 width=570) (actual time=17.081..17.082 rows=1 loops=1)
  ->  Sort  (cost=256.03..256.04 rows=1 width=570) (actual time=17.079..17.079 rows=1 loops=1)
        Sort Key: obj_created
        Sort Method:  top-N heapsort  Memory: 17kB
        ->  Bitmap Heap Scan on geodata_log  (cost=4.79..256.02 rows=1 width=570) (actual time=9.903..15.112 rows=829 loops=1)
              Recheck Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
              Filter: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
              ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.231..9.231 rows=2362 loops=1)
                    Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box
Total runtime: 17.184 ms

Second one on capture time:

CREATE INDEX i_geodata_log__obj_created
 ON geodata_log
 USING btree
 (obj_created);

Plan is:

Limit  (cost=40.44..40.45 rows=1 width=570) (actual time=15.288..15.288 rows=1 loops=1)
 ->  Sort  (cost=40.44..40.45 rows=1 width=570) (actual time=15.285..15.285 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=36.42..40.43 rows=1 width=570) (actual time=10.909..13.297 rows=829 loops=1)
             Recheck Cond: ((box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box) AND (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone))
             ->  BitmapAnd  (cost=36.42..36.42 rows=1 width=0) (actual time=10.751..10.751 rows=0 loops=1)
                   ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.235..9.235 rows=2362 loops=1)
                         Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
                   ->  Bitmap Index Scan on i_geodata_log__obj_created  (cost=0.00..31.38 rows=1483 width=0) (actual time=0.996..0.996 rows=3857 loops=1)
                         Index Cond: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
Total runtime: 15.392 ms

Despite the index scans the situation wishes to be desired. Is there another way? It is.

Take a look at postgres contribs and pay attention to cube and btree_gist. First provides a cube type - multidimentional cube of float point numbers. Second implements gist for int2, int4, timestamp and other common types.

cube will help us if we represent our space like float triplets (latitude, longitude, seconds_since_epoch). Btree allows to create mixed indexes. Let's install both contribs and test it.

cube requires additional column, let's name it area_time:

ALTER TABLE geodata_log
  ADD COLUMN area_time cube;

It has to be filled with 3D points (obj_point[0], obj_point[1], extract(epoch from obj_created)):

UPDATE geodata_log SET
   area_time = cube(ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)],
                    ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)]);

Create an index on it:

CREATE INDEX i_geodata_log__area_time
 ON geodata_log
 USING gist
 (area_time);

Change our query and look at the plan:

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   area_time <@ cube(ARRAY[50.857639595549, 30.337280273438, extract(epoch from '2008-07-04'::timestamp)],                               
                     ARRAY[55.450349029297, 38.715576171875, extract(epoch from now()::timestamp)]) 
ORDER BY
   obj_created 
LIMIT 1;  

Limit  (cost=265.36..265.36 rows=1 width=614) (actual time=5.312..5.313 rows=1 loops=1)   
 ->  Sort  (cost=265.36..265.52 rows=67 width=614) (actual time=5.310..5.310 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=8.83..265.02 rows=67 width=614) (actual time=0.794..3.190 rows=829 loops=1)
             Recheck Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)]))
             ->  Bitmap Index Scan on i_geodata_log__area_time  (cost=0.00..8.81 rows=67 width=0) (actual time=0.682..0.682 rows=829 loops=1)
                   Index Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)])) 
Total runtime: 5.420 ms

It's better, indeed. Now try btree_gist:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))     
   AND obj_created > '2008-07-04'
ORDER BY
   obj_created
LIMIT 1;

Limit  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.093 rows=1 loops=1)
 ->  Sort  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.092 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Index Scan using i_geodata_log__created_point on geodata_log  (cost=0.00..8.30 rows=1 width=570) (actual time=0.076..3.408 rows=829 loops=1)
             Index Cond: ((obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone) AND (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)) 
Total runtime: 5.170 ms

Quite good.

Making few tests in different situations I noticed that performance is almost the same in both cases but as cube requires additional column and trigger to fill it up I choose btree_gist.

What would you choose depends on your situation.

p.s. In the nearest future I'm going to publish a post named "Divide and conquer: active and arcive data problem" It'll be interesting I promise :)

Regards,
Sergey Konoplev

Пространство и время: применение cube и btree_gist

Привет

Хочу поделиться своим недавним опытом оптимизации работы с геометрическими данными в postgresql. На практике это решение показало очень хороший результат.

Допустим есть таблица для журналирования геоданных geodata_log, содержащая местоположение объекта obj_point типа point и время фиксации obj_created типа timestamp. По ней очень часто выполняется запрос получения самого "свежего" объекта в заданной области:

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875)) 
ORDER BY
    obj_created 
LIMIT 1;

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

EXPLAIN ANALYZE
SELECT * FROM
    geodata_log
WHERE
    box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))
    AND obj_created > '2008-07-04'
ORDER BY
    obj_created
LIMIT 1;

Ситуацию без индексов я не рассматриваю в силу очевидности появления плохого плана. И так, создаём индекс по времени фиксации и местоположению:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

И видим ошибку:

ERROR: data type timestamp with time zone has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

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

Создадим отдельный индекс по местоположению:

CREATE INDEX i_geodata_log__point
 ON geodata_log
 USING gist
 (box(obj_point, obj_point));

План запроса:

Limit  (cost=256.03..256.04 rows=1 width=570) (actual time=17.081..17.082 rows=1 loops=1)
  ->  Sort  (cost=256.03..256.04 rows=1 width=570) (actual time=17.079..17.079 rows=1 loops=1)
        Sort Key: obj_created
        Sort Method:  top-N heapsort  Memory: 17kB
        ->  Bitmap Heap Scan on geodata_log  (cost=4.79..256.02 rows=1 width=570) (actual time=9.903..15.112 rows=829 loops=1)
              Recheck Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
              Filter: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
              ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.231..9.231 rows=2362 loops=1)
                    Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box
Total runtime: 17.184 ms

И по времени фиксации:

CREATE INDEX i_geodata_log__obj_created
 ON geodata_log
 USING btree
 (obj_created);

План:

Limit  (cost=40.44..40.45 rows=1 width=570) (actual time=15.288..15.288 rows=1 loops=1)
 ->  Sort  (cost=40.44..40.45 rows=1 width=570) (actual time=15.285..15.285 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=36.42..40.43 rows=1 width=570) (actual time=10.909..13.297 rows=829 loops=1)
             Recheck Cond: ((box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box) AND (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone))
             ->  BitmapAnd  (cost=36.42..36.42 rows=1 width=0) (actual time=10.751..10.751 rows=0 loops=1)
                   ->  Bitmap Index Scan on i_geodata_log__point  (cost=0.00..4.79 rows=67 width=0) (actual time=9.235..9.235 rows=2362 loops=1)
                         Index Cond: (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)
                   ->  Bitmap Index Scan on i_geodata_log__obj_created  (cost=0.00..31.38 rows=1483 width=0) (actual time=0.996..0.996 rows=3857 loops=1)
                         Index Cond: (obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone)
Total runtime: 15.392 ms

Не смотря на индексные сканирования общая картина совсем не радует. Безвыходная ситуация? Нет.

Заглянув в стандартный набор сontrib'ов можно найти 2 интересных расширения
1. cube - добавляет тип cube - многомерный куб чисел с плавающей точкой
2. btree_gist - добавляет реализацию gist для int2, int4, timestamp и т.п.

cube может помочь ситуации, если представить наше пространство время тройками типа float (latitude, longitude, seconds_since_epoch), btree_gist просто позволит сделать смешанный индекс. Устанавливаем оба расширения, осталось проверить что быстрее.

Для cube добавим колонку area_time типа cube:

ALTER TABLE geodata_log
  ADD COLUMN area_time cube;

Заполним её нашими точечными кубами (obj_point[0], obj_point[1], extract(epoch from obj_created)):

UPDATE geodata_log SET
   area_time = cube(ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)],
                    ARRAY[obj_point[0], obj_point[1], extract(epoch from obj_created)]);

И создадим индекс:

CREATE INDEX i_geodata_log__area_time
 ON geodata_log
 USING gist
 (area_time);

Немного изменяем запрос и получаем план:

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   area_time <@ cube(ARRAY[50.857639595549, 30.337280273438, extract(epoch from '2008-07-04'::timestamp)],                               
                     ARRAY[55.450349029297, 38.715576171875, extract(epoch from now()::timestamp)]) 
ORDER BY
   obj_created 
LIMIT 1;  

Limit  (cost=265.36..265.36 rows=1 width=614) (actual time=5.312..5.313 rows=1 loops=1)   
 ->  Sort  (cost=265.36..265.52 rows=67 width=614) (actual time=5.310..5.310 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Bitmap Heap Scan on geodata_log  (cost=8.83..265.02 rows=67 width=614) (actual time=0.794..3.190 rows=829 loops=1)
             Recheck Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)]))
             ->  Bitmap Index Scan on i_geodata_log__area_time  (cost=0.00..8.81 rows=67 width=0) (actual time=0.682..0.682 rows=829 loops=1)
                   Index Cond: (area_time <@ cube('{50.857639595549,30.337280273438,1215115200}'::double precision[], ARRAY[55.450349029297::double precision, 38.715576171875::double precision, date_part('epoch'::text, (now())::timestamp without time zone)])) 
Total runtime: 5.420 ms

Уже лучше, определённо. Теперь пробуем btree_gist:

CREATE INDEX i_geodata_log__created_point
 ON geodata_log
 USING gist
 (obj_created, box(obj_point, obj_point));

EXPLAIN ANALYZE
SELECT  * FROM
   geodata_log
WHERE
   box(obj_point, obj_point) <@ box(point(50.857639595549, 30.337280273438), point(55.450349029297, 38.715576171875))     
   AND obj_created > '2008-07-04'
ORDER BY
   obj_created
LIMIT 1;

Limit  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.093 rows=1 loops=1)
 ->  Sort  (cost=8.31..8.32 rows=1 width=570) (actual time=5.092..5.092 rows=1 loops=1)
       Sort Key: obj_created
       Sort Method:  top-N heapsort  Memory: 17kB
       ->  Index Scan using i_geodata_log__created_point on geodata_log  (cost=0.00..8.30 rows=1 width=570) (actual time=0.076..3.408 rows=829 loops=1)
             Index Cond: ((obj_created > '2008-07-04 00:00:00+04'::timestamp with time zone) AND (box(obj_point, obj_point) <@ '(55.450349029297,38.715576171875),(50.857639595549,30.337280273438)'::box)) 
Total runtime: 5.170 ms

Совсем хорошо.

Сделав несколько испытаний в различных ситуациях, я отметил, что разницы в производительности между cube и btree_gist на моих объёмах данных практически нет, но т.к. cube потребует создание дополнительной колонки и триггера, который будет её обновлять, я выбрал btree_gist.

Что лучше выбрать вам будет зависит от вашей ситуации.

p.s. В ближайшее время опубликую пост "Отделяем мух от котлет: проблема активных и архивных данных", будет интересно, обещаю :)

С уважением,
Серегей Коноплёв