使用时间戳OVERLAPS和“PARTITION BY”加速PostgreSQL查询

CREATE TABLE feature_timeslice
(
  timeslice_id int NOT NULL,feature_id int NOT NULL,valid_time_begin timestamp NOT NULL,valid_time_end timestamp,sequence_number smallint,-- Some other columns
  CONSTRAINT pk_feature_timeslice PRIMARY KEY (timeslice_id)
  -- Some other constraints
)

CREATE INDEX ix_feature_timeslice_feature_id
ON feature_timeslice USING btree (feature_id);

然后,在timeslice_id上??将许多其他特定功能表连接到它：

CREATE TABLE specific_feature_timeslice
(
  timeslice_id int NOT NULL,-- Other columns
  CONSTRAINT pk_specific_feature_timeslice PRIMARY KEY (timeslice_id),CONSTRAINT fk_specific_feature_timeslice_feature_timeslice FOREIGN KEY (timeslice_id) REFERENCES feature_timeslice (timeslice_id)
)

可能存在具有重叠有效时段(开始/结束时间)的多个时间片,但具有最高sequence_number的时间片优先(再次,略微简化,但足够接近).我想有效地找到每个feature_id的当前有效行,所以我定义了一个视图,如下所示：

CREATE VIEW feature_timeslice_id_now
AS
    SELECT timeslice_id
    FROM
    (
        SELECT timeslice_id,rank() OVER
        (
            PARTITION BY feature_id
            ORDER BY sequence_number DESC,timeslice_id DESC
        )
        FROM feature_timeslice
        WHERE (current_timestamp AT TIME ZONE 'UTC','0'::interval) OVERLAPS (valid_time_begin,COALESCE(valid_time_end,'infinity'::timestamp))
    ) subq 
    WHERE subq.rank = 1

它通常像这样查询：

SELECT *
FROM specific_feature_timeslice sf
JOIN feature_timeslice_id_now n USING (timeslice_id)
WHERE sf.name = 'SOMETHING'

这可行,但它仍然有点太慢 – 需要1-2秒,即使可能只返回1-5行,因为specific_feature_timeslice标准通常会将其缩小很多. (加入多个功能视图的更复杂的查询会非常快速地变慢.)我无法弄清楚如何让PostgreSQL更有效地执行此操作.查询计划如下所示：

Join Filter: ((r.timeslice_id)::integer = (subq.timeslice_id)::integer)
  ->  Subquery Scan on subq  (cost=32034.36..37876.98 rows=835 width=4) (actual time=2086.125..5243.467 rows=250918 loops=1)
        Filter: (subq.rank = 1)
        ->  WindowAgg  (cost=32034.36..35790.33 rows=166932 width=10) (actual time=2086.110..4066.351 rows=250918 loops=1)
              ->  Sort  (cost=32034.36..32451.69 rows=166932 width=10) (actual time=2086.065..2654.971 rows=250918 loops=1)
                    Sort Key: feature_timeslice.feature_id,feature_timeslice.sequence_number,feature_timeslice.timeslice_id
                    Sort Method:  quicksort  Memory: 13898kB
                    ->  Seq Scan on feature_timeslice  (cost=0.00..17553.93 rows=166932 width=10) (actual time=287.270..1225.595 rows=250918 loops=1)
                          Filter: overlaps(timezone('UTC'::text,now()),(timezone('UTC'::text,now()) + '00:00:00'::interval),(valid_time_begin)::timestamp without time zone,COALESCE((valid_time_end)::timestamp without time zone,'infinity'::timestamp without time zone))
  ->  Materialize  (cost=0.00..1093.85 rows=2 width=139) (actual time=0.002..0.007 rows=2 loops=250918)
        ->  Seq Scan on specific_feature_timeslice sf  (cost=0.00..1093.84 rows=2 width=139) (actual time=1.958..7.674 rows=2 loops=1)
              Filter: ((name)::text = 'SOMETHING'::text)
Total runtime: 10319.875 ms

实际上,我想在任何给定时间内进行此查询,而不仅仅是当前时间.我有一个为此定义的函数,它将时间作为参数,但查询“现在”是最常见的情况,所以即使我只能加快速度,这将是一个很大的改进.

==编辑==

好的,我已经尝试按照两个答案的建议对表进行规范化 – 也就是说,我将valid_time_begin和valid_time_end移动到一个单独的表time_period中.我还用WHERE NOT EXISTS([更好的候选时间片])替换了窗口函数.在这个过程中,我也升级到了PostgreSQL 9.1.所有这些查询现在都快了两倍.查询计划与wildplasser的答案看起来相同.这很好,但不如我希望的那么好 – 从一个功能表中选择仍需要一秒钟.

理想情况下,我想利用功能WHERE条件的选择性,正如Erwin Brandstetter所说.如果我手工制作一个查询来做到这一点,我得到的时间是15-30毫秒.现在,它更像是它！手工制作的查询看起来像这样：

WITH filtered_feature AS
(
    SELECT *
    FROM specific_feature_timeslice sf
    JOIN feature_timeslice ft USING (timeslice_id)
    WHERE sf.name = 'SOMETHING'
)
SELECT *
FROM filtered_feature ff
JOIN
(
    SELECT timeslice_id
    FROM filtered_feature candidate
    JOIN time_period candidate_time ON candidate.valid_time_period_id = candidate_time.id
    WHERE ('2011-09-26','0'::interval) OVERLAPS (candidate_time.valid_time_begin,COALESCE(candidate_time.valid_time_end,'infinity'::timestamp))
        AND NOT EXISTS
        (
            SELECT *
            FROM filtered_feature better
            JOIN time_period better_time ON better.valid_time_period_id = better_time.id
            WHERE ('2011-09-26','0'::interval) OVERLAPS (better_time.valid_time_begin,COALESCE(better_time.valid_time_end,'infinity'::timestamp))
                AND better.feature_id = candidate.feature_id AND better.timeslice_id != candidate.timeslice_id
                AND better.sequence_number > candidate.sequence_number
        )
) AS ft ON ff.timeslice_id = ft.timeslice_id

不幸的是,这在普通查询中使用太大而且太复杂,它们可能会连接许多其他表.我需要一些方法将这个逻辑封装在一个函数(任意时间)或至少一个视图(当前时间),但我仍然无法弄清楚如何执行此操作,同时仍然让查询规划器首先过滤特定的功能.如果我只能将行集传递给函数 – 但据我所知,PostgreSQL不允许这样做.有任何想法吗？

==结论==

我最终使用PostgreSQL继承来解决这个问题(请参阅我的回答),但如果不是因为Erwin Brandstetter的答案,我就不会想出这个想法,所以赏金归于他. wildplasser的答案也非常有用,因为它允许我消除不必要的窗口功能,从而进一步加快它的速度.非常感谢你们俩！