Postgres查询有几种不同的过滤方式

Question

我有一个很大的postgres查询，其中有很多联接，我想用几种不同的方式进行过滤。我有一个执行联接的中心功能：

create function my_big_function(p_limit int, p_offset, p_filter_ids int[])
returns setof my_type
language sql
immutable
returns null on null input 
as
$$
select my_column_list
from 
(
select my_filter_id
from  unnest(p_filter_ids)
order by my_filter_id
limit p_limit, offset p_offset
) f(my_filter_id)
inner join... several other tables (using indexed columns)

然后我有一系列简短的函数，用来构建过滤器ID的列表，例如：

create or replace my_filter_id_function(p_some_id int)
returns int[]
language sql
immutable
returns null on null input
as
$$
select array_agg(my_filter_id) from my_table where some_id = p_some_id
$$;

这使我能够快速添加几个过滤函数，并将结果数组作为参数输入到大查询中，而不必在很多地方重复大查询。

select * from my_big_function(1000, 0, my_filter_function1(p_some_id));
select * from my_big_function(1000, 0, my_filter_function2(p_some_other_id));
select * from my_big_function(1000, 0, my_filter_function3(p_yet_another_id));

问题是，当筛选函数返回的值数组变大(大约1000行(时，我的查询速度很慢。我想这是因为大查询必须按顺序排序，然后使用非索引结果进行连接？有没有更好的方法来创建一个通用查询，我可以将ID输入到其中，以不同的方式对其进行过滤？

Answer 1

我会避免使用大型数组，因为打包和拆包它们会变得昂贵。

但我想说的是，这里的主要问题是将查询拆分为不同的函数，这会阻止优化器同时处理整个查询并制定有效的执行计划。

如果希望避免重复查询的某些部分，正确的工具不是函数，而是视图。当执行查询时，视图会被其定义所替换，因此优化器可以为整个查询找到一个好的计划。

不要陷入定义一个连接所有表的"世界观"的陷阱。视图应该只包含查询中实际需要的表。