Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17

Unfortunately, I have not been able to create a script that reproduces the
issue. I ran EXPLAIN with a LIMIT clause and again without a LIMIT clause,
save the output to files and then compared them. I am not good at
understanding the EXPLAIN output. Here is the difference between the two
outputs:

diff 'Explain with limit.sql' 'Explain without limit.sql'
1c1,4
< Limit (cost=10248.11..10248.81 rows=1 width=99)
---
> Hash Right Join (cost=10248.23..10248.68 rows=1 width=99)
> Hash Cond: (pef.facility_key = t_1.facility_key)
> Filter: (t.devstatus_date >= CASE WHEN
(((max(pef.facility_type_start_date)) IS NOT NULL) AND
((max(pef.facility_type_start_date)) > ((max(t_1.devstatus_date)) - '1
mon'::interval))) T
HE
> N ((max(pef.facility_type_start_date)))::timestamp without time zone ELSE
((max(t_1.devstatus_date)) - '1 mon'::interval) END)
71,75c74,82
< -> Nested Loop (cost=45.71..46.41 rows=1 width=99)
< Join Filter: ((t.facility_key = t_1.facility_key) AND
(t.devstatus_date >= CASE WHEN (((max(pef.facility_type_start_date)) IS NOT
NULL) AND ((max(pef.facility_type_start_date)) >
(
< (max(t_1.devstatus_date)) - '1 mon'::interval))) THEN
((max(pef.facility_type_start_date)))::timestamp without time zone ELSE
((max(t_1.devstatus_date)) - '1 mon'::interval) END))
< -> Nested Loop Left Join (cost=45.71..46.36 rows=1 width=19)
< Join Filter: (pef.facility_key = t_1.facility_key)
---
> -> HashAggregate (cost=1.36..1.54 rows=18 width=8)
> Group Key: pef.facility_key
> -> Seq Scan on ppt_exception_facilities pef (cost=0.00..1.27
rows=18 width=8)
> Filter: (facility_type_start_date < CURRENT_DATE)
> -> Hash (cost=44.45..44.45 rows=1 width=103)
> -> Nested Loop (cost=44.35..44.45 rows=1 width=103)
> Join Filter: (t.facility_key = t_1.facility_key)
> -> CTE Scan on t (cost=0.00..0.03 rows=1 width=92)
> Filter: (devstatus_date <= CURRENT_DATE)
92,98c99
< -> HashAggregate (cost=1.36..1.54 rows=18 width=8)
< Group Key: pef.facility_key
< -> Seq Scan on ppt_exception_facilities pef
(cost=0.00..1.27 rows=18 width=8)
< Filter: (facility_type_start_date <
CURRENT_DATE)
< -> CTE Scan on t (cost=0.00..0.03 rows=1 width=92)
< Filter: (devstatus_date <= CURRENT_DATE)
< (93 rows)
---
> (94 rows)

Here is the individual output; First with the LIMIT clause:

Limit (cost=10248.11..10248.81 rows=1 width=99)
CTE t
-> Unique (cost=10202.38..10202.40 rows=1 width=92)
-> Sort (cost=10202.38..10202.38 rows=1 width=92)
Sort Key: t_2.facility_key, t_2.traineeid, e.ndc_emp_id,
t_2.facility_eod, (CASE WHEN ((ltia.tia_code ~~ 'CPCIT%'::text) AND
(ltia.tia_code <> 'TX-INTRA-FACILITY'::text)) T
HEN 'CPC-IT'::text WHEN (ltia.tia_code = 'TX-INTRA-FACILITY'::text) THEN
'INTRA-FACILITY'::text ELSE ltia.tia_code END), t_2.trainee_start_date,
(CASE WHEN (t_2.devstatus_date < t_2.trainee
_start_date) THEN t_2.trainee_start_date ELSE t_2.devstatus_date END),
(CASE WHEN ((NOT lds.tempstat) AND (lds.devstatusid <> 1)) THEN 'Did Not
Complete'::text WHEN lds.tempstat THEN 'In Pr
ogress'::text ELSE 'Completed'::text END), (CASE WHEN (t_2.devstatus_date <
t_2.trainee_start_date) THEN 0 ELSE (t_2.devstatus_date -
t_2.trainee_start_date) END)
-> Gather (cost=6302.90..10202.37 rows=1 width=92)
Workers Planned: 1
-> Nested Loop (cost=5302.90..9202.27 rows=1
width=92)
-> Nested Loop (cost=5302.76..9201.91 rows=2
width=33)
-> Nested Loop (cost=5302.34..9201.37
rows=1 width=33)
-> Nested Loop
(cost=5302.21..9201.21 rows=1 width=37)
-> Hash Join
(cost=5302.07..9201.05 rows=1 width=41)
Hash Cond:
(t_2.facility_key = history_facility_level.facility_key)
-> Hash Join
(cost=5257.47..8829.54 rows=86445 width=41)
Hash Cond:
(ts.traineeid = t_2.traineeid)
-> Parallel Seq
Scan on trainee_stage ts (cost=0.00..2383.45 rows=86445 width=12)
-> Hash
(cost=4776.18..4776.18 rows=38503 width=29)
-> Hash
Join (cost=2272.26..4776.18 rows=38503 width=29)
Hash
Cond: (h.explid = el.explid)
->
Hash Join (cost=2271.10..4612.76 rows=38503 width=33)

Hash Cond: (t_2.hiresourceid = h.hiresourceid)

-> Hash Join (cost=2269.25..4498.57 rows=38503 width=33)

Hash Cond: (t_2.devstatusid = lds.devstatusid)

-> Hash Join (cost=2267.62..4380.57 rows=38503 width=32)

Hash Cond: (t_2.devstatus_date = dd2.caldate)

-> Hash Join (cost=1570.89..3154.43 rows=38503 width=32)

Hash Cond: (t_2.facility_eod = dd1.caldate)

-> Hash Join (cost=874.16..1928.28 rows=38503 width=32)

Hash Cond: (t_2.ntdid = e.ntdid)

-> Seq Scan on trainee t_2 (cost=0.00..953.03
rows=38503 width=32)

-> Hash (cost=574.07..574.07 rows=24007 width=8)

-> Seq Scan on ntd_employee e
(cost=0.00..574.07 rows=24007 width=8)

-> Hash (cost=472.89..472.89 rows=17907 width=4)

-> Index Only Scan using date_dim_caldate_idx on
date_dim dd1 (cost=0.29..472.89 rows=17907 width=4)

-> Hash (cost=472.89..472.89 rows=17907 width=4)

-> Index Only Scan using date_dim_caldate_idx on
date_dim dd2 (cost=0.29..472.89 rows=17907width=4)

-> Hash (cost=1.28..1.28 rows=28 width=5)

-> Seq Scan on status_dev lds (cost=0.00..1.28 rows=28
width=5)

-> Hash (cost=1.38..1.38 rows=38 width=8)

-> Seq Scan on hire_source h (cost=0.00..1.38 rows=38 width=8)
->
Hash (cost=1.07..1.07 rows=7 width=4)

-> Seq Scan on experience_label el (cost=0.00..1.07 rows=7 width=4)
-> Hash
(cost=44.59..44.59 rows=1 width=12)
-> Hash Join
(cost=26.70..44.59 rows=1 width=12)
Hash Cond:
((upper(f.facility_type_number) = upper(lft.facility_type_number)) AND
(upper(f.facility_type_number_desc) = upper(lft.facility_type_desc)))
-> Nested
Loop (cost=25.30..43.14 rows=3 width=32)
Join
Filter: (f.facility_key = history_facility_level.facility_key)
->
Hash Join (cost=25.02..40.29 rows=3 width=8)

Hash Cond: ((hfl.facility_key = history_facility_level.facility_key) AND
(hfl.facility_atc_level_start_date =
(max(history_facility_level.facility_atc_level_start_date))))

-> Seq Scan on history_facility_level hfl (cost=0.00..12.08 rows=608
width=8)

-> Hash (cost=19.08..19.08 rows=396 width=8)

-> HashAggregate (cost=15.12..19.08 rows=396 width=8)

Group Key: history_facility_level.facility_key

-> Seq Scan on history_facility_level (cost=0.00..12.08
rows=608 width=8)
->
Index Scan using facility_dim_facility_key_key on facility_dim f
(cost=0.28..0.94 rows=1 width=24)

Index Cond: (facility_key = hfl.facility_key)
-> Hash
(cost=1.16..1.16 rows=16 width=21)
->
Seq Scan on facility_type lft (cost=0.00..1.16 rows=16 width=21)
-> Index Only Scan using
training_stage_pkey on training_stage lts (cost=0.14..0.16 rows=1 width=4)
Index Cond: (stageid =
ts.stageid)
-> Index Only Scan using
stage_status_pkey on status_stage lss (cost=0.13..0.15 rows=1 width=4)
Index Cond: (stagestatusid =
ts.stagestatusid)
-> Index Only Scan using
trainee_instance_attributes_traineeid_tiaid_key on
trainee_instance_attributes tia (cost=0.42..0.50 rows=4 width=8)
Index Cond: (traineeid =
ts.traineeid)
-> Index Scan using
training_instance_attribute_pkey on training_instance_attribute ltia
(cost=0.14..0.17 rows=1 width=17)
Index Cond: (tiaid = tia.tiaid)
Filter: ((tia_code = 'NEWHIRE'::text) OR
(tia_code ~~ 'CPCIT%'::text) OR (tia_code = 'TX-INTRA-FACILITY'::text))
-> Nested Loop (cost=45.71..46.41 rows=1 width=99)
Join Filter: ((t.facility_key = t_1.facility_key) AND
(t.devstatus_date >= CASE WHEN (((max(pef.facility_type_start_date)) IS NOT
NULL) AND ((max(pef.facility_type_start_date)) > (
(max(t_1.devstatus_date)) - '1 mon'::interval))) THEN
((max(pef.facility_type_start_date)))::timestamp without time zone ELSE
((max(t_1.devstatus_date)) - '1 mon'::interval) END))
-> Nested Loop Left Join (cost=45.71..46.36 rows=1 width=19)
Join Filter: (pef.facility_key = t_1.facility_key)
-> GroupAggregate (cost=44.35..44.42 rows=1 width=15)
Group Key: t_1.facility_key, fd.facility_code
-> Incremental Sort (cost=44.35..44.39 rows=2
width=15)
Sort Key: t_1.facility_key, fd.facility_code
Presorted Key: t_1.facility_key
-> Nested Loop Left Join (cost=28.89..44.34
rows=1 width=15)
-> CTE Scan on t t_1 (cost=0.00..0.03
rows=1 width=8)
Filter: (((status =
'Completed'::text) OR (status = 'Did Not Complete'::text)) AND
(devstatus_date <= CURRENT_DATE))
-> Hash Join (cost=28.89..44.30 rows=1
width=11)
Hash Cond: (hft.facility_key =
fd.facility_key)
-> HashAggregate
(cost=20.58..27.39 rows=681 width=8)
Group Key: hft.facility_key
-> Seq Scan on
history_facility_type hft (cost=0.00..18.46 rows=846 width=4)
-> Hash (cost=8.30..8.30 rows=1
width=11)
-> Index Scan using
facility_dim_facility_key_key on facility_dim fd (cost=0.28..8.30 rows=1
width=11)
Index Cond:
(facility_key = t_1.facility_key)
-> HashAggregate (cost=1.36..1.54 rows=18 width=8)
Group Key: pef.facility_key
-> Seq Scan on ppt_exception_facilities pef
(cost=0.00..1.27 rows=18 width=8)
Filter: (facility_type_start_date < CURRENT_DATE)
-> CTE Scan on t (cost=0.00..0.03 rows=1 width=92)
Filter: (devstatus_date <= CURRENT_DATE)
(93 rows)

Here without the LIMIT clause:

Hash Right Join (cost=10248.23..10248.68 rows=1 width=99)
Hash Cond: (pef.facility_key = t_1.facility_key)
Filter: (t.devstatus_date >= CASE WHEN
(((max(pef.facility_type_start_date)) IS NOT NULL) AND
((max(pef.facility_type_start_date)) > ((max(t_1.devstatus_date)) - '1
mon'::interval))) THE
N ((max(pef.facility_type_start_date)))::timestamp without time zone ELSE
((max(t_1.devstatus_date)) - '1 mon'::interval) END)
CTE t
-> Unique (cost=10202.38..10202.40 rows=1 width=92)
-> Sort (cost=10202.38..10202.38 rows=1 width=92)
Sort Key: t_2.facility_key, t_2.traineeid, e.ndc_emp_id,
t_2.facility_eod, (CASE WHEN ((ltia.tia_code ~~ 'CPCIT%'::text) AND
(ltia.tia_code <> 'TX-INTRA-FACILITY'::text)) T
HEN 'CPC-IT'::text WHEN (ltia.tia_code = 'TX-INTRA-FACILITY'::text) THEN
'INTRA-FACILITY'::text ELSE ltia.tia_code END), t_2.trainee_start_date,
(CASE WHEN (t_2.devstatus_date < t_2.trainee
_start_date) THEN t_2.trainee_start_date ELSE t_2.devstatus_date END),
(CASE WHEN ((NOT lds.tempstat) AND (lds.devstatusid <> 1)) THEN 'Did Not
Complete'::text WHEN lds.tempstat THEN 'In Pr
ogress'::text ELSE 'Completed'::text END), (CASE WHEN (t_2.devstatus_date <
t_2.trainee_start_date) THEN 0 ELSE (t_2.devstatus_date -
t_2.trainee_start_date) END)
-> Gather (cost=6302.90..10202.37 rows=1 width=92)
Workers Planned: 1
-> Nested Loop (cost=5302.90..9202.27 rows=1
width=92)
-> Nested Loop (cost=5302.76..9201.91 rows=2
width=33)
-> Nested Loop (cost=5302.34..9201.37
rows=1 width=33)
-> Nested Loop
(cost=5302.21..9201.21 rows=1 width=37)
-> Hash Join
(cost=5302.07..9201.05 rows=1 width=41)
Hash Cond:
(t_2.facility_key = history_facility_level.facility_key)
-> Hash Join
(cost=5257.47..8829.54 rows=86445 width=41)
Hash Cond:
(ts.traineeid = t_2.traineeid)
-> Parallel Seq
Scan on trainee_stage ts (cost=0.00..2383.45 rows=86445 width=12)
-> Hash
(cost=4776.18..4776.18 rows=38503 width=29)
-> Hash
Join (cost=2272.26..4776.18 rows=38503 width=29)
Hash
Cond: (h.explid = el.explid)
->
Hash Join (cost=2271.10..4612.76 rows=38503 width=33)

Hash Cond: (t_2.hiresourceid = h.hiresourceid)

-> Hash Join (cost=2269.25..4498.57 rows=38503 width=33)

Hash Cond: (t_2.devstatusid = lds.devstatusid)

-> Hash Join (cost=2267.62..4380.57 rows=38503 width=32)

Hash Cond: (t_2.devstatus_date = dd2.caldate)

-> Hash Join (cost=1570.89..3154.43 rows=38503 width=32)

Hash Cond: (t_2.facility_eod = dd1.caldate)

-> Hash Join (cost=874.16..1928.28 rows=38503 width=32)

Hash Cond: (t_2.ntdid = e.ntdid)

-> Seq Scan on trainee t_2 (cost=0.00..953.03
rows=38503 width=32)

-> Hash (cost=574.07..574.07 rows=24007 width=8)

-> Seq Scan on ntd_employee e
(cost=0.00..574.07 rows=24007 width=8)

-> Hash (cost=472.89..472.89 rows=17907 width=4)

-> Index Only Scan using date_dim_caldate_idx on
date_dim dd1 (cost=0.29..472.89 rows=17907 width=4)

-> Hash (cost=472.89..472.89 rows=17907 width=4)

-> Index Only Scan using date_dim_caldate_idx on
date_dim dd2 (cost=0.29..472.89 rows=17907width=4)

-> Hash (cost=1.28..1.28 rows=28 width=5)

-> Seq Scan on status_dev lds (cost=0.00..1.28 rows=28
width=5)

-> Hash (cost=1.38..1.38 rows=38 width=8)

-> Seq Scan on hire_source h (cost=0.00..1.38 rows=38 width=8)
->
Hash (cost=1.07..1.07 rows=7 width=4)

-> Seq Scan on experience_label el (cost=0.00..1.07 rows=7 width=4)
-> Hash
(cost=44.59..44.59 rows=1 width=12)
-> Hash Join
(cost=26.70..44.59 rows=1 width=12)
Hash Cond:
((upper(f.facility_type_number) = upper(lft.facility_type_number)) AND
(upper(f.facility_type_number_desc) = upper(lft.facility_type_desc)))
-> Nested
Loop (cost=25.30..43.14 rows=3 width=32)
Join
Filter: (f.facility_key = history_facility_level.facility_key)
->
Hash Join (cost=25.02..40.29 rows=3 width=8)

Hash Cond: ((hfl.facility_key = history_facility_level.facility_key) AND
(hfl.facility_atc_level_start_date =
(max(history_facility_level.facility_atc_level_start_date))))

-> Seq Scan on history_facility_level hfl (cost=0.00..12.08 rows=608
width=8)

-> Hash (cost=19.08..19.08 rows=396 width=8)

-> HashAggregate (cost=15.12..19.08 rows=396 width=8)

Group Key: history_facility_level.facility_key

-> Seq Scan on history_facility_level (cost=0.00..12.08
rows=608 width=8)
->
Index Scan using facility_dim_facility_key_key on facility_dim f
(cost=0.28..0.94 rows=1 width=24)

Index Cond: (facility_key = hfl.facility_key)
-> Hash
(cost=1.16..1.16 rows=16 width=21)
->
Seq Scan on facility_type lft (cost=0.00..1.16 rows=16 width=21)
-> Index Only Scan using
training_stage_pkey on training_stage lts (cost=0.14..0.16 rows=1 width=4)
Index Cond: (stageid =
ts.stageid)
-> Index Only Scan using
stage_status_pkey on status_stage lss (cost=0.13..0.15 rows=1 width=4)
Index Cond: (stagestatusid =
ts.stagestatusid)
-> Index Only Scan using
trainee_instance_attributes_traineeid_tiaid_key on
trainee_instance_attributes tia (cost=0.42..0.50 rows=4 width=8)
Index Cond: (traineeid =
ts.traineeid)
-> Index Scan using
training_instance_attribute_pkey on training_instance_attribute ltia
(cost=0.14..0.17 rows=1 width=17)
Index Cond: (tiaid = tia.tiaid)
Filter: ((tia_code = 'NEWHIRE'::text) OR
(tia_code ~~ 'CPCIT%'::text) OR (tia_code = 'TX-INTRA-FACILITY'::text))
-> HashAggregate (cost=1.36..1.54 rows=18 width=8)
Group Key: pef.facility_key
-> Seq Scan on ppt_exception_facilities pef (cost=0.00..1.27
rows=18 width=8)
Filter: (facility_type_start_date < CURRENT_DATE)
-> Hash (cost=44.45..44.45 rows=1 width=103)
-> Nested Loop (cost=44.35..44.45 rows=1 width=103)
Join Filter: (t.facility_key = t_1.facility_key)
-> CTE Scan on t (cost=0.00..0.03 rows=1 width=92)
Filter: (devstatus_date <= CURRENT_DATE)
-> GroupAggregate (cost=44.35..44.42 rows=1 width=15)
Group Key: t_1.facility_key, fd.facility_code
-> Incremental Sort (cost=44.35..44.39 rows=2
width=15)
Sort Key: t_1.facility_key, fd.facility_code
Presorted Key: t_1.facility_key
-> Nested Loop Left Join (cost=28.89..44.34
rows=1 width=15)
-> CTE Scan on t t_1 (cost=0.00..0.03
rows=1 width=8)
Filter: (((status =
'Completed'::text) OR (status = 'Did Not Complete'::text)) AND
(devstatus_date <= CURRENT_DATE))
-> Hash Join (cost=28.89..44.30 rows=1
width=11)
Hash Cond: (hft.facility_key =
fd.facility_key)
-> HashAggregate
(cost=20.58..27.39 rows=681 width=8)
Group Key: hft.facility_key
-> Seq Scan on
history_facility_type hft (cost=0.00..18.46 rows=846 width=4)
-> Hash (cost=8.30..8.30 rows=1
width=11)
-> Index Scan using
facility_dim_facility_key_key on facility_dim fd (cost=0.28..8.30 rows=1
width=11)
Index Cond:
(facility_key = t_1.facility_key)
(94 rows)

On Sat, Jun 14, 2025 at 10:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Lowell Hought <lowell(dot)hought(at)gmail(dot)com> writes:
> > Why would it return with a LIMIT clause, but not without the LIMIT
> clause?
>
> [ shrug... ] I still suppose this is due to a poor choice of plan
> in the no-LIMIT case, but you've yet to provide the info needed
> for someone else to reproduce the problem. You could try comparing
> EXPLAIN output in the LIMIT and no-LIMIT cases.
>
> regards, tom lane
>