В ожидании 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 запросы выглядят великолепно.

Фикс облака тэгов

Решил избавиться от количества записей в облаке тэгов. По моему так оно получше смотрится. Как считаете?

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

Перевод 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