Блог

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

Таблицы, используемые для минимального STR — log это основная таблица данных, каждая строка содержит increment absolute значение или. Абсолютное значение сбрасывает текущее counter значение с новым базовым значением. Оконные функции должны обрабатывать все журналы для данного account_id , чтобы вычислить правильный текущий итог. View использует подзапрос, чтобы гарантировать, что базовые log строки не фильтруются ts , в противном случае это нарушило бы работу оконной функции.

 CREATE TABLE account(
  id serial,
  name VARCHAR(100)
);

CREATE TABLE log(
  id serial,
  absolute int,
  incremental int,
  account_id int,
  ts timestamp,
  PRIMARY KEY(id),
  CONSTRAINT fk_account
      FOREIGN KEY(account_id)
    REFERENCES account(id)

);

CREATE FUNCTION get_running_total_func(
    aggregated_total int,
    absolute int,
    incremental int
) RETURNS int
   LANGUAGE sql IMMUTABLE CALLED ON NULL INPUT AS
$
   SELECT
        CASE
            WHEN absolute IS NOT NULL THEN absolute 
            ELSE COALESCE(aggregated_total, 0)   incremental
        END
$;

CREATE AGGREGATE get_running_total(integer, integer) (
   sfunc = get_running_total_func,
   stype = integer
);
 

Медленный просмотр:

  CREATE VIEW test_view
 (
   log_id,
   running_value,
   account_id,
   ts
 )
 AS
     SELECT log_running.* FROM
 (SELECT
   log.id,
     get_running_total(
       log.absolute,
       log.incremental
   )
   OVER(
       PARTITION BY log.account_id
       ORDER BY log.ts RANGE UNBOUNDED PRECEDING
   ),
   account.id,
   ts
   FROM log log JOIN account account ON log.account_id=account.id
  ) AS log_running;
CREATE VIEW
postgres=# EXPLAIN ANALYZE SELECT * FROM test_view WHERE account_id=1;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on log_running  (cost=12734.02..15981.48 rows=1 width=20) (actual time=7510.851..16122.404 rows=20 loops=1)
   Filter: (log_running.id_1 = 1)
   Rows Removed by Filter: 99902
   ->  WindowAgg  (cost=12734.02..14732.46 rows=99922 width=32) (actual time=7510.830..14438.783 rows=99922 loops=1)
         ->  Sort  (cost=12734.02..12983.82 rows=99922 width=28) (actual time=7510.628..9312.399 rows=99922 loops=1)
               Sort Key: log.account_id, log.ts
               Sort Method: external merge  Disk: 3328kB
               ->  Hash Join  (cost=143.50..2042.24 rows=99922 width=28) (actual time=169.941..5431.650 rows=99922 loops=1)
                     Hash Cond: (log.account_id = account.id)
                     ->  Seq Scan on log  (cost=0.00..1636.22 rows=99922 width=24) (actual time=0.063..1697.802 rows=99922 loops=1)
                     ->  Hash  (cost=81.00..81.00 rows=5000 width=4) (actual time=169.837..169.865 rows=5000 loops=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 240kB
                           ->  Seq Scan on account  (cost=0.00..81.00 rows=5000 width=4) (actual time=0.017..84.639 rows=5000 loops=1)
 Planning Time: 0.199 ms
 Execution Time: 16127.275 ms
(15 rows)
 

Быстрое изменение только account.id для просмотра -> log.account_id (!):

  CREATE VIEW test_view
 (
  log_id,
  running_value,
  account_id,
  ts
 )
 AS
     SELECT log_running.* FROM
 (SELECT
   log.id,
     get_running_total(
       log.absolute,
       log.incremental
   )
   OVER(
       PARTITION BY log.account_id
       ORDER BY log.ts RANGE UNBOUNDED PRECEDING
   ),
   log.account_id,
   ts
   FROM log log JOIN account account ON log.account_id=account.id
   ) AS log_running;
CREATE VIEW
postgres=# EXPLAIN ANALYZE SELECT * FROM test_view WHERE account_id=1;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on log_running  (cost=1894.96..1895.56 rows=20 width=20) (actual time=34.718..45.958 rows=20 loops=1)
   ->  WindowAgg  (cost=1894.96..1895.36 rows=20 width=28) (actual time=34.691..45.307 rows=20 loops=1)
         ->  Sort  (cost=1894.96..1895.01 rows=20 width=24) (actual time=34.367..35.925 rows=20 loops=1)
               Sort Key: log.ts
               Sort Method: quicksort  Memory: 26kB
               ->  Nested Loop  (cost=0.28..1894.53 rows=20 width=24) (actual time=0.542..34.066 rows=20 loops=1)
                     ->  Index Only Scan using account_pkey on account  (cost=0.28..8.30 rows=1 width=4) (actual time=0.025..0.054 rows=1 loops=1)
                           Index Cond: (id = 1)
                           Heap Fetches: 1
                     ->  Seq Scan on log  (cost=0.00..1886.03 rows=20 width=24) (actual time=0.195..32.937 rows=20 loops=1)
                           Filter: (account_id = 1)
                           Rows Removed by Filter: 99902
 Planning Time: 0.297 ms
 Execution Time: 47.300 ms
(14 rows)
 

Это ошибка в реализации PostgreSQL? Похоже, что это изменение в определении представления вообще не должно влиять на производительность, PostgreSQL должен иметь возможность фильтровать данные перед применением функции окна для всего набора данных.