Hello Pgsql-performance, To not flood network with many parameters I send only one and use `WITH` hack to reuse value inside query: WITH _app_period AS ( select app_period() ), ready AS ( SELECT min( lower( o.app_period ) ) OVER ( PARTITION BY agreement_id ) <@ (select * from _app_period) AS new_order, max( upper( o.app_period ) ) OVER ( PARTITION BY agreement_id ) <@ (select * from _app_period) AS del_order ,o.* FROM "order_bt" o LEFT JOIN acc_ready( 'Usage', (select * from _app_period), o ) acc_u ON acc_u.ready LEFT JOIN acc_ready( 'Invoice', (select * from _app_period), o ) acc_i ON acc_i.ready LEFT JOIN agreement a ON a.id = o.agreement_id LEFT JOIN xcheck c ON c.doc_id = o.id and c.doctype = 'OrderDetail' WHERE o.sys_period @> sys_time() AND o.app_period && (select * from _app_period) ) SELECT * FROM ready https://explain.depesz.com/s/kDCJ3#query but becaues of this `acc_ready` is not inlined and I get perfomance downgrade. Can we mark here (select * from _app_period) subquery as constant and allow to pass inline condition: >none of the actual arguments contain volatile expressions or subselects https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions this subselect is not volatile and could be expanded to constant What do you think about this proposition? I expect it to spent 0.5ms instead of 14ms like here (I put app_period() explicitly) https://explain.depesz.com/s/iNTw 30 times faster! EXPLAIN( ANALYSE, FORMAT JSON, VERBOSE, settings, buffers ) WITH ready AS ( SELECT min( lower( o.app_period ) ) OVER ( PARTITION BY agreement_id ) <@ app_period() AS new_order, max( upper( o.app_period ) ) OVER ( PARTITION BY agreement_id ) <@ app_period() AS del_order ,o.* FROM "order_bt" o LEFT JOIN acc_ready( 'Usage', app_period(), o ) acc_u ON acc_u.ready LEFT JOIN acc_ready( 'Invoice', app_period(), o ) acc_i ON acc_i.ready LEFT JOIN agreement a ON a.id = o.agreement_id LEFT JOIN xcheck c ON c.doc_id = o.id and c.doctype = 'OrderDetail' WHERE o.sys_period @> sys_time() AND o.app_period && app_period() ) SELECT * FROM ready -- Best regards, Eugen Konkov
