Зачем CASCADE в DROP INDEX

Читая рассылку pgsql-general, cделал для себя интересное замечание. Вопрос был - для чего указывать CASCADE в DROP INDEX, что может зависеть от индекса? Ответ - если индекс UNIQUE, то FOREIGN KEY.

Источник тут.

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

Я использовал этот запрос при переводе gist-индексов на gin.

SELECT indexdef
FROM pg_indexes
WHERE indexdef ~ 'USING (gist|gin)';

Не оптимальное чтение

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

Также удобно бывает использовать ограничение по размеру (в примере ниже оно закомментировано). Оно помогает отфильтровывать таблицы, по которым последовательные чтения потенциально выгодны. Тут необходимо смотреть на величину, и вообще на факт, необходимости использования по ситуации, проведя несколько экспериментов.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
seq_scan AS ss, -- последовательных чтений
idx_scan AS is, -- индексных чтений
seq_scan + idx_scan AS re, -- всего чтений
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
-- WHERE pg_relation_size(relid) > 1 * 1024 * 1024 -- ограничение по размеру
ORDER BY
length(seq_scan::text) DESC,
pg_relation_size(relid) DESC
LIMIT 40;

Не используемые индексы

Данный запрос предназначен для выявления индексов претендентов на удаление. В начале отображаются наиболее редко используемые большие индексы.

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

SELECT
idstat.schemaname AS sch, -- схема
idstat.relname AS tab, -- таблица
indexrelname AS idx, -- индекс
idstat.idx_scan AS iis, -- число сканирований по этому индексу
pg_size_pretty(pg_relation_size(indexrelid)) AS isize, -- размер индекса
tabstat.idx_scan AS tis, -- индексных чтений по таблице
tabstat.seq_scan AS tss, -- последовательных чтений по таблице
tabstat.seq_scan + tabstat.idx_scan AS tre, -- чтений по таблице
n_tup_upd + n_tup_ins + n_tup_del AS twr, -- операций записи
pg_size_pretty(pg_relation_size(idstat.relid)) AS tsize -- размер таблицы
FROM
pg_stat_user_indexes AS idstat
JOIN pg_indexes ON
indexrelname = indexname AND
idstat.schemaname = pg_indexes.schemaname
JOIN pg_stat_user_tables AS tabstat ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
ORDER BY
idstat.idx_scan,
pg_relation_size(indexrelid) DESC
LIMIT 20;

Почему мой индекс не используется

Перевод Why is my index not being used с Postgres OnLine Journal

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

В этой статье мы кратко опишем основные причины и попробуем упорядочить их по статистической значимости.

Если кто не в курсе, то узнать о том, что ваш индекс используется или не используется можно с помощью инструкций EXPLAIN, EXPLAIN ANALYZE, или, например, продвинутого инструмента графического объяснения планов в PgAdmin.

Ок, допустим запрос не использует индекс. Почему и что можно с этим сделать?

Проблема: Устаревшая статистика.
Сейчас это менее вероятно в связи с тем, что по умолчанию auto-vacuum включён. Но если в таблице только что было обновлено/добавлено/удалено много записей, или был добавлен новый индекс, то есть вероятность, что во время выполнения запроса новая статистика ещё не соберётся.

Решение: vacuum analyze verbose ;
Я добавил verbose для того, что бы видеть что делает сборка мусора/статистики, и, если вы настолько же нетерпеливы как и я и ваша таблица довольно большая, то здорово видеть что что-то в самом деле происходит. Так же можно выполнить vacuum analyze verbose без указания таблицы, если необходимо провести профилактику всей БД.

Проблема: Планировщик решил, что последовательное сканирование быстрее чем индексное.
Это может случиться если а) ваша таблица относительно мала, или поле, по которому вы индексируете, содержит много дубликатов.

Решение: В случае использования, например boolean, когда 50% данных содержат одно значение, а вторая половина другое, индексирование будет не очень полезным. Однако, это неплохая причина для использования частичных индексов, например для индексирования только активных данных.

Проблема: Вы создали индекс, который не совместим с тем, как вы фильтруете данные. Есть ряд таких случаев.

1. LIKE '%me' никогда не будет использовать индексы, но можно использовать LIKE 'me%'.

2. Ловушка с upper/lower - если определить индекс как:

CREATE INDEX idx_faults_name ON faults USING btree(fault_name);

и выполнить такой запрос

SELECT * FROM faults where UPPER(fault_name) LIKE 'CAR%'

то индекс не будет использоваться. Тут необходимо создать индекс следующим образом:

CREATE INDEX idx_faults_name ON faults USING btree(upper(fault_name));

3. Этого не избежал даже я, всегда полезно почитать о проблемах других людей в новостных группах, т.к. вы узнаёте о вещах, о которых даже не подозревали. Если ваш сервер инициализирован с C-локалью, выше сказанное опять же не будет работать. Подобный вопрос недавно было в группе pgsql-novice, где Tom Lane дал подробный ответ, и это, по моему, затрагивает многих. Ситуацию возможно исправит:

CREATE INDEX idx_faults_uname_varchar_pattern ON faults USING btree(upper(fault_name) varchar_pattern_ops);

Однако, даже в этом случае может потребоваться учитывать описанное ниже:

Не совсем понятно является ли это проблемой кодировки БД, самих данных или разницы версий 8.3 b 8.2. Похоже, что в 8.3 в конкретных случаях с данными в кодировке UTF-8 требуется точное сравнение, однако в подобных ситуациях с 8.2 в SQL-ASCII достаточно varchar_pattern_ops как для точного сравнения так и для LIKE.

CREATE INDEX idx_faults_uname ON faults USING btree(upper(fault_name)); 

SELECT fault_name from  faults
WHERE upper(fault_name) IN('CASCADIA ABDUCTION', 'CABIN FEVER');

4. Ошибки новичков, когда делается что-нибудь типа создания индекса по дате и сравнение текста с этой датой приведённой к тексту.

Проблема: Не все индексы могу быть использованы.
Не смотря на то, что с версии 8.1+ поддерживаются Bitmap Index Scan, позволяющие множеству индексов быть использованными в запросе путём создания в памяти bitmap индексов, если индексов много, не ждите, что будут использоваться все ожидаемые. Иногда сканирование таблицы бывает эффективнее.

Проблема: Планировщик не идеален.
Решение: Рыдать и молиться о более светлых временах. На самом деле я был очень потрясён возможностями планировщика Postgresql в сравнении с другими СУБД. Некоторые говорят, если бы только тут были "хинты", я бы сделал это работающим быстрее. Я же думаю, что хинты это плохая идея и лучшим решением было бы сделать планировщик лучше. Проблема хинтов в том, что они отодвигают на второй план одну хорошую вещь, идею того, что СУБД знает состояние данных лучше чем человек и постоянно обновляет это знание. Хинты могут быстро стать не актуальными, в то время когда хороший планировщик будет постоянно менять стратегию по мере изменений в БД, и это то что делает программирование баз данных уникальным.

Leo Hsu and Regina Obe

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

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

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