I already asked why column is not pushed down and optimization is not applied.
And get detailed explanation at mail list
There were problem when I used whole-row vars. But here I do not use them.
Query goes work slow when I do LEFT JOIN or put this LEFT JOIN inside CTE.
When I run query directly (or put condition inside) it takes only 6ms to run:
WITH ready AS (
SELECT
agreement_id, sys_period, app_period,
acc_i.ready as acc_i_ready,
acc_i.acc_period as acc_i_period,
acc_i.consumed_period as acc_i_consumed_period,
acc_u.ready as acc_u_ready,
acc_u.acc_period as acc_u_period,
acc_u.consumed_period as acc_u_consumed_period
FROM "order_bt" o
LEFT JOIN acc_ready( 'Invoice', app_period(), o ) acc_i ON acc_i.ready
LEFT JOIN acc_ready( 'Usage', app_period(), o ) acc_u ON acc_u.ready
WHERE o.sys_period @> sys_time() AND o.app_period && app_period()
AND agreement_id = 1736
)
SELECT *
FROM ready
When I just repeat condition outside it takes 45ms
WITH ready AS (
SELECT
agreement_id, sys_period, app_period,
acc_i.ready as acc_i_ready,
acc_i.acc_period as acc_i_period,
acc_i.consumed_period as acc_i_consumed_period,
acc_u.ready as acc_u_ready,
acc_u.acc_period as acc_u_period,
acc_u.consumed_period as acc_u_consumed_period
FROM "order_bt" o
LEFT JOIN acc_ready( 'Invoice', app_period(), o ) acc_i ON acc_i.ready
LEFT JOIN acc_ready( 'Usage', app_period(), o ) acc_u ON acc_u.ready
WHERE o.sys_period @> sys_time() AND o.app_period && app_period()
AND agreement_id = 1736
)
SELECT *
FROM ready
-- NOTICE: I only repeat this!
WHERE sys_period @> sys_time() AND app_period && app_period()
AND agreement_id = 1736
When I split condition it takes 671ms:
WITH ready AS (
SELECT
agreement_id, sys_period, app_period,
acc_i.ready as acc_i_ready,
acc_i.acc_period as acc_i_period,
acc_i.consumed_period as acc_i_consumed_period,
acc_u.ready as acc_u_ready,
acc_u.acc_period as acc_u_period,
acc_u.consumed_period as acc_u_consumed_period
FROM "order_bt" o
LEFT JOIN acc_ready( 'Invoice', app_period(), o ) acc_i ON acc_i.ready
LEFT JOIN acc_ready( 'Usage', app_period(), o ) acc_u ON acc_u.ready
WHERE o.sys_period @> sys_time() AND o.app_period && app_period()
)
SELECT *
FROM ready
WHERE
agreement_id = 1736 -- Unfortunately this is not used for index scan =( Why???
When I move all conditions outside it takes already 11084ms:
(hmmm, but I do not break inline condition, does I?)
WITH ready AS (
SELECT
agreement_id, sys_period, app_period,
acc_i.ready as acc_i_ready,
acc_i.acc_period as acc_i_period,
acc_i.consumed_period as acc_i_consumed_period,
acc_u.ready as acc_u_ready,
acc_u.acc_period as acc_u_period,
acc_u.consumed_period as acc_u_consumed_period
FROM "order_bt" o
LEFT JOIN acc_ready( 'Invoice', app_period(), o ) acc_i ON acc_i.ready
LEFT JOIN acc_ready( 'Usage', app_period(), o ) acc_u ON acc_u.ready
)
SELECT *
FROM ready
WHERE sys_period @> sys_time() AND app_period && app_period()
AND agreement_id = 1736
And when I remove LEFT JOIN it takes only 5ms!
WITH ready AS (
SELECT
agreement_id, sys_period, app_period
FROM "order_bt" o
)
SELECT *
FROM ready
WHERE sys_period @> sys_time() AND app_period && app_period()
AND agreement_id = 1736
Compare: 6ms VS 45ms VS 671ms VS 11084ms VS 5ms
Same on depesz: 6ms VS 45ms VS 671ms VS 11084ms VS 5ms
Why second, third and forth queries are not optimized like first/last queries?
Is there some workarounds for forth case to make it work fast?
(like to select additionally fields which is described at mailing list (see links above))
PS. Second case seems to me counter intuitive and is not expected behavior
Server version is 13.1
*first - when condition is only inside CTE
*second - when this condition copied outside CTE
UPD Further investigations:
try I1
Here, when function is volatile for first and second cases BitmapHeapScan is applied over two indexes:
order_idx_agreement_id, order_id_sys_period_app_period_excl
try I2
After changing acc_ready function to STABLE,
for first query BitmapHeapScan is used.
for second query IndexScan is used (by mistake?).
Compare 7ms VS 11ms
Here we see that inlining saves for us 2ms
and 34ms (because no JIT generation).
But still it takes additionally 4ms (by mistake?) when I repeat condition outside.
(Here I do not expect extra 4ms of time, because inside CTE condition was unchanged, so there should not be changes in time too, should there?)
try I3
Finnaly when I drop order_id_sys_period_app_period_excl index, the BitmapHeapScan is used again (instead of slower IndexScan).
This is most fast query which takes only 3ms
Still opened questions:
Why IndexScan was used at I2 section while BitmapHeapScan was used at I1 section?
Why BitmapIndexScans are not applied one after another as it were done at I3 section? (fastest case)
