的文档Pg的窗口函数说:
窗口函数考虑的行是查询的 FROM 子句生成的“虚拟表”的行，并通过其 WHERE、GROUP BY 和 HAVING 子句（如果有）进行过滤。例如，由于不满足 WHERE 条件而被删除的行不会被任何窗口函数看到。查询可以包含多个窗口函数，这些窗口函数通过不同的 OVER 子句以不同的方式对数据进行切片，但它们都作用于该虚拟表定义的同一行集合。
但是，我没有看到这一点。在我看来，选择过滤器非常靠近左边距和顶部（最后完成的事情）。
=# EXPLAIN SELECT * FROM chrome_nvd.view_options where fkey_style = 303451;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Subquery Scan view_options (cost=2098450.26..2142926.28 rows=14825 width=180)
Filter: (view_options.fkey_style = 303451)
-> Sort (cost=2098450.26..2105862.93 rows=2965068 width=189)
Sort Key: o.sequence
-> WindowAgg (cost=1446776.02..1506077.38 rows=2965068 width=189)
-> Sort (cost=1446776.02..1454188.69 rows=2965068 width=189)
Sort Key: h.name, k.name
-> WindowAgg (cost=802514.45..854403.14 rows=2965068 width=189)
-> Sort (cost=802514.45..809927.12 rows=2965068 width=189)
Sort Key: h.name
-> Hash Join (cost=18.52..210141.57 rows=2965068 width=189)
Hash Cond: (o.fkey_opt_header = h.id)
-> Hash Join (cost=3.72..169357.09 rows=2965068 width=166)
Hash Cond: (o.fkey_opt_kind = k.id)
-> Seq Scan on options o (cost=0.00..128583.68 rows=2965068 width=156)
-> Hash (cost=2.21..2.21 rows=121 width=18)
-> Seq Scan on opt_kind k (cost=0.00..2.21 rows=121 width=18)
-> Hash (cost=8.80..8.80 rows=480 width=31)
-> Seq Scan on opt_header h (cost=0.00..8.80 rows=480 width=31)
(19 rows)
这两个 WindowAgg 实质上将计划更改为似乎永远无法完成的更快的计划
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan view_options (cost=329.47..330.42 rows=76 width=164) (actual time=20.263..20.403 rows=42 loops=1)
-> Sort (cost=329.47..329.66 rows=76 width=189) (actual time=20.258..20.300 rows=42 loops=1)
Sort Key: o.sequence
Sort Method: quicksort Memory: 35kB
-> Hash Join (cost=18.52..327.10 rows=76 width=189) (actual time=19.427..19.961 rows=42 loops=1)
Hash Cond: (o.fkey_opt_header = h.id)
-> Hash Join (cost=3.72..311.25 rows=76 width=166) (actual time=17.679..18.085 rows=42 loops=1)
Hash Cond: (o.fkey_opt_kind = k.id)
-> Index Scan using options_pkey on options o (cost=0.00..306.48 rows=76 width=156) (actual time=17.152..17.410 rows=42 loops=1)
Index Cond: (fkey_style = 303451)
-> Hash (cost=2.21..2.21 rows=121 width=18) (actual time=0.432..0.432 rows=121 loops=1)
-> Seq Scan on opt_kind k (cost=0.00..2.21 rows=121 width=18) (actual time=0.042..0.196 rows=121 loops=1)
-> Hash (cost=8.80..8.80 rows=480 width=31) (actual time=1.687..1.687 rows=480 loops=1)
-> Seq Scan on opt_header h (cost=0.00..8.80 rows=480 width=31) (actual time=0.030..0.748 rows=480 loops=1)
Total runtime: 20.893 ms
(15 rows)
这是怎么回事？我该如何解决？我正在使用 Postgresql 8.4.8。这是实际视图正在执行的操作：
SELECT o.fkey_style, h.name AS header, k.name AS kind
, o.code, o.name AS option_name, o.description
, count(*) OVER (PARTITION BY h.name) AS header_count
, count(*) OVER (PARTITION BY h.name, k.name) AS header_kind_count
FROM chrome_nvd.options o
JOIN chrome_nvd.opt_header h ON h.id = o.fkey_opt_header
JOIN chrome_nvd.opt_kind k ON k.id = o.fkey_opt_kind
ORDER BY o.sequence;
