Warning! Do not migrate to 8.3.4

Hi all,

Serious bug was found in 8.3.4 yesterday. If your DB deals with finance, privat security and so on - I advise you not to migrate to 8.3.4 postgres version. 8.3.3 does not contain this bug.

Additional info you can find here http://archives.postgresql.org/pgsql-general/2008-10/msg00845.php

Важно! Не спешите переходить на 8.3.4

Всем привет,

Вчера в версии postgresql 8.3.4 был найден серьёзный баг. Если ваша БД ответственна за финансовые операции, безопасность и т.п. - не советую вам обновляться до 8.3.4. Версия 8.3.3 этой ошибки не содержит.

Подробности тут http://archives.postgresql.org/pgsql-general/2008-10/msg00845.php

В ожидании 8.4 - новый FSM (Free Space Map)

Перевод Waiting for 8.4 - new FSM (Free Space Map) с select * from depesz;

30го сентября Heikki Linnakangas применил свой патч, который вносит изменения в FSM:

Переписан FSM. Вместо того, чтобы полагаться на фиксированный (указанный) размер 
сегмента разделяемой памяти, информация о свободном месте теперь хранится в 
отдельном FSM отношении для каждого отношения БД (за исключением hash индексов; они 
не используют FSM). 

Это устраняет необходимость в max_fsm_relations и max_fsm_pages GUC параметрах; 
удалены все их вхождения в бэкэнд, initdb и  документацию.

Переписан contrib/pg_freespacemap в соответствии с новой FSM реализацией. Так же
представлен новый вариант функции get_raw_page(regclass, int4, int4) в 
contrib/pageinspect, который позволяет увидеть страницы любого отношения, и новая 
функция fsm_page_contents() для проверки новых FSM страниц.

Что это значит для DBA?

Для начала, отпадает необходимость в настройке 2х параметров в postgresql.conf: max_fsm_pages и max_fsm_relations.

Эти параметры (когда установлены не корректно) могут сделать vacuum менее эффективным (что происходит очень часто). Т.ч., в основе, это хорошо, что их больше не будет.

Что ещё? Чтобы сделать это Haikki пришлось реализовать так называемые "дополнительные отношения" ("Relation forks"). И это важно, потому что (насколько я понимаю, если я понял не правильно, пожалуйста, поправьте меня) они могут (и наверняка будут) использоваться для хранения "карт видимости" ("visibility maps"), которые сделают vacuum более быстрым (и возможно повлияют на индексные сканирования, но это только моя догадка).

Что же это за "дополнительные отношения"? Всё просто. Как известно, таблицы хранятся в файлах следующим образом:

$PGDATA/base/<database-oid>/<table-filenode>

Иногда они имеют суфиксы .1, .2 и т.д. - в случае если размер таблицы (или индекса) превышеает гигабайт.

Дополнительные отношения добавляют второй набор файлов, именуемых:

<table-filenode>_1

Для FSM код "1" (но ходят разговоры о текстовых кодах).

Пример:

# create table x (id int4);
CREATE TABLE
 
# select oid from pg_database where datname = 'depesz';
oid
-------
16385
(1 row)
 
# select relfilenode from pg_class where relname = 'x' and relkind = 'r';
relfilenode
-------------
16387
(1 row)
 
=> ls -l $PGDATA/base/16385/16387*
-rw------- 1 pgdba pgdba 0 2008-10-04 12:50 /home/pgdba/data/base/16385/16387
-rw------- 1 pgdba pgdba 0 2008-10-04 12:50 /home/pgdba/data/base/16385/16387_1

Конечно же, теперь FSM хранит полную информацию и не ограничен в размере. Интересно, как много места он занимает. Давайте проверим:

# insert into x (id) select * from generate_series(1,100000);
INSERT 0 100000
 
# \! ls -l $PGDATA/base/16385/16387*
-rw------- 1 pgdba pgdba 3219456 2008-10-04 12:53 /home/pgdba/data/base/16385/16387
-rw------- 1 pgdba pgdba 24576 2008-10-04 12:53 /home/pgdba/data/base/16385/16387_1
# insert into x (id) select * from generate_series(1,100000);
INSERT 0 100000
 
# \! ls -l $PGDATA/base/16385/16387*
-rw------- 1 pgdba pgdba 6430720 2008-10-04 12:53 /home/pgdba/data/base/16385/16387
-rw------- 1 pgdba pgdba 24576 2008-10-04 12:53 /home/pgdba/data/base/16385/16387_1
# insert into x (id) select * from generate_series(1,100000);
INSERT 0 100000
 
# \! ls -l $PGDATA/base/16385/16387*
-rw------- 1 pgdba pgdba 9641984 2008-10-04 12:53 /home/pgdba/data/base/16385/16387
-rw------- 1 pgdba pgdba 24576 2008-10-04 12:53 /home/pgdba/data/base/16385/16387_1

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

Но, может это из-за того, что таблица очень маленькая, всего 1177 страниц. Проверим на чём-нибудь большем:

# drop table x;
DROP TABLE
 
# create table x (id int4, dummy_text text);
CREATE TABLE
 
# alter table x alter column dummy_text set storage plain;
ALTER TABLE
 
# select relfilenode from pg_class where relname = 'x' and relkind = 'r';
relfilenode
-------------
16408
(1 row)

На заметку: я сделал alter column set storage plain для хранения всех данных из dummy_text в главной таблице, без компресси - эффективное отключение TOAST.

# insert into x select i, repeat('depesz', 500) from generate_series(1,100000) as i;
INSERT 0 100000
 
# \! ls -l $PGDATA/base/16385/16408*
-rw------- 1 pgdba pgdba 409600000 2008-10-04 13:05 /home/pgdba/data/base/16385/16408
-rw------- 1 pgdba pgdba 122880 2008-10-04 13:05 /home/pgdba/data/base/16385/16408_1

И что случится, если я сделаю update 50% записей?

# update x set dummy_text = repeat('_test_', 500) where id <= 50000;
UPDATE 50000
 
# \! ls -l $PGDATA/base/16385/16408*
-rw------- 1 pgdba pgdba 614400000 2008-10-04 13:09 /home/pgdba/data/base/16385/16408
-rw------- 1 pgdba pgdba 172032 2008-10-04 13:08 /home/pgdba/data/base/16385/16408_1

Это говорит, что излишек данных на диске составляет около 0.03%, что является незначительным.

Выгоды? У нас теперь одним поводом для беспокойства меньше (слишком маленькие значения параметров FSM) и основание для будущего кода, который сделает vacuum быстрее. Намного быстрее.

В ожидании 8.4 - упорядоченная загрузка данных в дамп

Перевод Waiting for 8.4 - ordered data loading in pg_dump с select * from depesz;

Отличный (и, надо сказать, давно ожидаемый) патч от Tom Lane:

Теперь pg_dump --data-only пытается упорядочить дампы таблиц таким образом,
что таблицы, на которые ссылаются вторичные ключи, выгружаются раньше таблиц, 
содержащих эти ключи. Это помогает обходить сбои, возникающие при загрузке 
данных,которые ссылаются на ещё не загруженные данные. Когда такое упорядочивание 
не возможно, в случае циклических зависимостей или ссылок на самих себя, выводит 
NOTICE для предупреждения об этом пользователя.

Что это означает в действительности?

Начнём с простого допущения - этот патч затрагивает выгрузку только данных (--data-only). Так что, если вы это не используете, то вам оно ничем не поможет. Простите.

Но, если используете, и у вас "пропатченая" версия postgres, вот что произойдёт:

Во первых, создадим кое-какие таблицы:

# create table b (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for serial column "b.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
 
# create table a (id serial primary key, b_id int4 references b (id));
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE

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

# insert into b (id) values (DEFAULT), (DEFAULT), (DEFAULT);
INSERT 0 3
# insert into a (b_id) values (1), (2), (3);
INSERT 0 3

Всё просто, вот данные:

# select * from b;
id
----
1
2
3
(3 rows)
 
# select * from a;
id | b_id
----+------
1 | 1
2 | 2
3 | 3
(3 rows)

Вот что произойдёт если я сделаю дамп без патча:

=> pg_dump --data-only
...
COPY a (id, b_id) FROM stdin;
1 1
2 2
3 3
\.
...
COPY b (id) FROM stdin;
1
2
3
\.
...

Печально - данные будут загружены в таблицу a (с FK на b) перед тем, как загрузится b.

Конечно же данные могут быть загружены иначе, через выключение триггеров вторичных ключей (ALTER TABLE … DISABLE TRIGGER), но это сложновато, и определённо не "круто".

С новым патчем дамп выглядит иначе:

...
COPY b (id) FROM stdin;
1
2
3
\.
...
COPY a (id, b_id) FROM stdin;
1 1
2 2
3 3
\.
...

Порядок теперь правильный. Конечно это не сработает в случае циклической зависимости:

# create table a (id serial primary key);
# create table b (id serial primary key, a_id int4 references a(id));
# alter table a add column b_id int4 references b (id);
# insert into a (id) values (DEFAULT);
# insert into b (id, a_id) values (1, 1);
# insert into a (b_id) values (1);

Будет выведено предупреждение:

pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump: a
pg_dump: b
pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

Хорошее дополнение. И респект Tom'у.

В ожидании 8.4 - lc_collation и lc_ctype уровня БД

Перевод Waiting for 8.4 - database-level lc_collation and lc_ctype с select * from depesz;

23 сентября Heikki Linnakangas применил патч, который написал Radek Strnad (на самом деле была применена его доработанная версия).

Что он делает? Патч позволяет добавлять (действительно!) разные collation order и character categories для разных БД.

До этого надо было устанавливать LC_COLLATE и LC_CTYPE при инициализации кластера (initdb), и в дальнейшем эти параметры невозможно было изменить. БД инициализированная с LATIN2 не будет корректно работать с данными в UTF-8.

Теперь всё меняется:

Делает LC_COLLATE и LC_CTYPE параметрами уровня БД. Порядок 
сортировки (collation) и класс символов (ctype) сейчас, подобно 
кодировке, хранятся в новых колонках datcollate и datctype 
таблицы database.

Это доработанная мной версия патча Radek Strnad'а.

Что мы получаем?

Например, предположим, что есть инстанс PostgreSQL инициализированный с локалью "C":

# \l
List of databases
Name       | Owner  | Encoding  | Collation | Ctype | Access Privileges
-----------+--------+-----------+-----------+-------+----------------------------
depesz     | depesz | SQL_ASCII | C         | C     |
postgres   | pgdba  | SQL_ASCII | C         | C     |
template0  | pgdba  | SQL_ASCII | C         | C     | {=c/pgdba,pgdba=CTc/pgdba}
template1  | pgdba  | SQL_ASCII | C         | C     | {=c/pgdba,pgdba=CTc/pgdba}
(4 rows)

Т.к. "C" ничего не знает, например, о польских символах, не возможно будет корректно сделать сортировку по польскому тексту. Это же касается работы upper():

# set client_encoding = 'UTF-8';
SET
 
# select c, upper(c) from (values ('a'), ('ć'), ('e'), ('ź'), ('x'), ('ł'), ('ś'), ('w')) as x (c) order by c;
c  | upper
---+-------
a  | A
e  | E
w  | W
x  | X
ć  | ć
ł  | ł
ś  | ś
ź  | ź
(8 rows)

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

С версией postgres <= 8.3 мне бы потребовалось переинициализировать кластер (reinitdb), и, соответственно, сконвертировать все базы для новых языковых параметров, что немного проблематично.

К счастью, с версии 8.4 я смогу просто добавить новую базу с другими параметрами:

# CREATE DATABASE depesz_pl with encoding 'utf8' collate 'pl_PL.UTF-8' ctype 'pl_PL.UTF-8' template template0;
CREATE DATABASE

Единственная проблема в необходимости использования template0. Иначе я получу:

# CREATE DATABASE depesz_pl with encoding 'utf8' collate 'pl_PL.UTF-8' ctype 'pl_PL.UTF-8';
ERROR: new collation is incompatible with the collation of the template database (C)
HINT: Use the same collation as in the template database, or use template0 as template

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

И так, теперь у нас есть новая БД, попробуем на ней наш тестовый запрос:

# \c depesz_pl
You are now connected to database "depesz_pl".
 
# show client_encoding ;
client_encoding
-----------------
UTF8
(1 row)
 
# select c, upper(c) from (values ('a'), ('ć'), ('e'), ('ź'), ('x'), ('ł'), ('ś'), ('w')) as x (c) order by c;
c  | upper
---+-------
a  | A
ć  | Ć
e  | E
ł  | Ł
ś  | Ś
w  | W
x  | X
ź  | Ź
(8 rows)

ДА! Заработало!

Также, команда \l выдаст новую информацию:

# \l
List of databases
Name       | Owner  | Encoding  | Collation   | Ctype       | Access Privileges
-----------+--------+-----------+-------------+-------------+----------------------------
depesz     | depesz | SQL_ASCII | C           | C           |
depesz_pl  | depesz | UTF8      | pl_PL.UTF-8 | pl_PL.UTF-8 |
postgres   | pgdba  | SQL_ASCII | C           | C           |
template0  | pgdba  | SQL_ASCII | C           | C           | {=c/pgdba,pgdba=CTc/pgdba}
template1  | pgdba  | SQL_ASCII | C           | C           | {=c/pgdba,pgdba=CTc/pgdba}
(5 rows)

Одна вещь, которую надо принять - нельзя изменить collation/ctype существующей базы. Причина тому очень проста: индексы зависят от collation (и могут зависеть от ctype). Т.о. изменение collation/ctype потребуют переиндексации всех данных. Если это технически возможно, вы сможете сделать это с помошью дампа базы, создания новой с желаемой локалью и загрузки этого дампа.

Конечно же, ещё далеко до полноценного функционирования PostgreSQL в мультиязычном окружении, но по крайней мере это шаг в правильном направлении. Долгожданный и важный шаг.

Комментарии

Steve, 29 сентября 2008 в 06:43
Вы упомянули "ещё далеко до полноценного функционирования PostgreSQL в мультиязычном окружении". Не могли бы вы рассказать в чём нехватка полноценного функционирования и где могут быть проблемы?

depesz, 29 сентября 2008 в 09:55
@Steve:
Для того чтобы сделать это полнофункциональным, необходима поддержка collation/ctype на более мелких объектах, чем база данных: таблицы или колонки.

Например, для сортировки по тексту на нескольких языках. Конечно, можно везде использовать utf8, но это приводит к дополнительным затратам на конвертацию текста при использовании различных кодировок.

postgresql error: more than one function named

Привет

В последнне время, анализируя свой Google Analytics, я заметил, что самым частым поисковым запросом от русскоязычной аудитории к Google, который приводит на мой блог является "postgresql error: more than one function named". Если вы пришли на эту страницу по такому запросу, оставьте, пож., описание ситуации в которой возникает данная ошибка и свой e-mail в комментариях. Я постараюсь найти ответ и обязательно напишу вам.

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

IRC: #postgresql-ru@irc.freenode.net (KOI8-R)
Jabber: postgresmen@conference.jabber.org
Web: http://forum.postgresmen.ru

Всё это абсолютно свободно

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

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. В ближайшее время опубликую пост "Отделяем мух от котлет: проблема активных и архивных данных", будет интересно, обещаю :)

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