Count (select 1) subquery as constant

[Eugen Konkov]

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 xcheckc   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 xcheckc   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

Re: transaction blocking on COMMIT

[Bob Jolliffe]

No brtfs.  We are going to try turning off synchronous_commit
temporarily to see if there are underlying I/O issues.

On Mon, 24 May 2021 at 22:59, Alexey M Boltenkov  wrote:
>
> On 05/24/21 19:24, Christophe Pettus wrote:
> >
> >> On May 24, 2021, at 09:22, Bob Jolliffe  wrote:
> >>
> >> It is hard to say as it only happens for 30s couple of times per day.
> >> Everything does return to normal after the blocking transaction is
> >> committed.  It could be a disk thing or even a network issue (the java
> >> app is on a different machine to the db).  But I never saw
> >> transactions blocked in commit before so was wondering if there is any
> >> rational set of reasons why it might do that.
> > One thing you can check is to turn off synchronous_commit (understanding 
> > the possibility of "time loss" in the event of a system crash).  If that 
> > mitigates the problem, the issue is likely the I/O subsystem blocking 
> > during the fsync() operation.
> >
> >
> Just a question. Is there a btrfs(with compression maybe) around? 30
> seconds is a commit(file system) timeout for btrfs. Some processes like
> btrfs cleaner/allocate/worker on top of CPU/io use?
>