首页 文章

优化Postgresql函数财务月日期

提问于
浏览 次
1

我编写了以下函数，该函数与下表一起使用，该函数存储公司的会计月定义，但是当用于按会计月份汇总数据时，此函数似乎相当慢 . 任何人都可以就如何加快速度提出一些指示吗？

Edit: I am still having some bottlenecks with this function. I have rewritten it to remove some fluff, but it is still significantly slower than just joining on the fiscal_months table in my queries. However I'd still rather use a function for ease of use, and so I don't have to rewrite a few dozen queries that are still using it.

创建或替换函数get_fiscal_month（日期）RETURNS整数AS $ BODY $ SELECT案例提取时（年份为1美元）<2008年提取（月数来自$ 1）::整数ELSE（选择月份来自fiscal_months WHERE EXTRACT（年份来自$ 1）=年份AND $ 1> = start_date AND $ 1 <= end_date）END $ BODY $ LANGUAGE sql VOLATILE COST 100;

EDIT : May 6, 2014 (Rewrote function to be slightly faster)

CREATE OR REPLACE FUNCTION get_fiscal_month(date)
    RETURNS integer AS
    $BODY$
    SELECT month FROM fiscal_months WHERE $1 >= start_date AND $1 <= end_date
    $BODY$
LANGUAGE sql STABLE
COST 1000;

Changes
1.删除案例日期<2008年（我在2008年之前没有任何数据）
2.删除了WHERE（从日期提取年份）=年（这是一个不必要的步骤）
3.将VOLATILE改为STABLE
4.将成本从100更改为1000

CREATE TABLE fiscal_months（id serial NOT NULL，year integer NOT NULL，month integer NOT NULL，start_date date NOT NULL，end_date date NOT NULL，CONSTRAINT fiscal_months_pkey PRIMARY KEY（id），CONSTRAINT fiscal_months_ukey UNIQUE（year，month））

CREATE TABLE fiscal_months
(
id serial NOT NULL,
year integer NOT NULL,
month integer NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL,
CONSTRAINT fiscal_months_pkey PRIMARY KEY (id),
CONSTRAINT fiscal_months_ukey UNIQUE (year, month),
CONSTRAINT fiscal_months_ukey_end UNIQUE (end_date),
CONSTRAINT fiscal_months_ukey_start UNIQUE (start_date)
)

CREATE INDEX fiscal_months_index_bothdates
    ON fiscal_months USING btree (start_date, end_date);

CREATE INDEX fiscal_months_index_test2
    ON fiscal_months USING btree (start_date);

Changes
1.从SO用户添加以下评论的索引 .

Table Stats
顺序扫描48018264
顺序元组阅读4006572336
Index Scans 3251027 Index Tuples Fetched 27236663
元组插入0
元组更新0
元组已删除0
元组HOT更新0
直播元组86
死元组0
堆块读取3047
堆块命中51266249
索引块阅读13
索引块命中3251026 Toast Blocks Read
吐司块命中
Toast Index Blocks读取
Toast Index Blocks Hit
Last Vacuum 2014-05-05 16：46：54.087489-05
最后一次Autovacuum
最后分析2014-05-06 13：23：47.709653-05
最后一次自动分析2014-05-05 16：47：29.248862-05
表大小8192字节
吐司表大小无
索引大小96 kB

Example Data
年月份Start_Date End_Date

2014 1 "2014-01-01" "2014-01-24"
2014 2 "2014-01-25" "2014-02-21"
2014 3 "2014-02-22" "2014-03-28"
2014 4 "2014-03-29" "2014-04-25"
2014 5 "2014-04-26" "2014-05-23"
2014 6 "2014-05-24" "2014-06-27"

附：我正在使用Postgresql 8.3
附：我正在使用Postgresql 8.4

sql postgresql sql-function

2 回答

  • 2

    我编写了以下函数，该函数与下表一起使用，该函数存储公司的会计月定义，但是当用于按会计月汇总数据时，此函数似乎相当慢 . 任何人都可以就如何加快速度提出一些指示吗？

    我认为你的问题在于你如何使用这个功能 . 如果您以这种方式聚合数据，则表示您拒绝计划者加入fiscal_month（） .

    您应该做什么IMO，只有在 data >= '2008-01-01' 时才在聚合查询的UNION部分调用fiscal_month（） . 将它封装在一个函数中意味着它可能不是可内联的，因此不是能够做一个散列连接，而是坚持使用嵌套循环，这几乎肯定是你的问题所在 .

    回复于
  • 0

    该参考表中有哪些数据？财政月份通常可以从不使用查询的日期确定 .

    例如，如果您的会计年度2012年从2012年4月到2013年3月，则您可以通过以下方式确定当前会计月度：

    to_char(date_trunc('month',current_date) - interval '3' month, 'YYYY MM')

    我会考虑以这种方式实现算法，最好是在SQL中（当然，使用CASE，因为它看起来像2009年的财政月逻辑变化） .

    回复于

相关问题