Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17 - Mailing list pgsql-bugs
| From | Lowell Hought |
|---|---|
| Subject | Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17 |
| Date | |
| Msg-id | CAJtAGPriJrVYmWKyPaMhmHCnLqA4Rs7tv2=r_zZ2sTpg_Z-ZNw@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17 (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-bugs |
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)
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)
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)
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@sss.pgh.pa.us> wrote:
Lowell Hought <lowell.hought@gmail.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
pgsql-bugs by date: