Thread
-
BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
PG Bug reporting form <noreply@postgresql.org> — 2025-06-07T16:08:44Z
The following bug has been logged on the website: Bug reference: 18950 Logged by: Lowell Hought Email address: lowell.hought@gmail.com PostgreSQL version: 17.5 Operating system: Red Hat Enterprise Linux release 8.10 (Ootpa) Description: A pgsql function that returns Table is relatively simple. It accepts two arguments; a date and an interval. It uses these to arguments in a query that returns a result set. This function worked in Postgresql 16.1 and Postgresql 16.9. However, it does not work in versions 17.0 nor in 17.5 Here is my build sequence for version 17.5, the last version I tested. ./configure --prefix=/usr/local/pgsql/17/ --with-openssl --with-ldap --with-systemd --with-libxml --with-libxslt gmake world gmake check : # All 222 tests passed. sudo gmake install-world su root mkdir /usr/local/pgsql/17/data chown postgres /usr/local/pgsql/17/data su postgres /usr/local/pgsql/17/bin/initdb -D /usr/local/pgsql/17/data Success. You can now start the database server using: /usr/local/pgsql/17/bin/pg_ctl -D /usr/local/pgsql/17/data -l logfile start /usr/local/pgsql/17/bin/pg_ctl -D /usr/local/pgsql/17/data -l /usr/local/pgsql/17/data/logfile start /usr/local/pgsql/17/bin/createdb test /usr/local/pgsql/17/bin/psql test psql (17.5) Type "help" for help. Restore from backup: /usr/local/pgsql/17/bin/psql -d test -Upostgres -f /bak/db/db_backup.sql Then I run psql and execute this command: SELECT * FROM report.GetReportPoolTrainees(CURRENT_DATE, '1 year'); The result is that even after 12 hours the function does not return. There is no response whatsoever. I tested this both on Redhat 8.10 and on Red Hat Enterprise Linux release 8.10 (Ootpa) and on Rocky Linux release 8.7 (Green Obsidian) with identical results. On both systems the function call works as expected in version 16, but in version 17 it never returns. Another interesting fact: I run the query contained in within the function, it executes as expected and returns the expected results. It is only when called via the function that it fails. Here is the function definition: CREATE OR REPLACE FUNCTION report.GetReportPoolTrainees( venddate DATE, vtimespan INTERVAL ) RETURNS TABLE ( facility_key INT, facility_code TEXT, traineeid INT, ndc_emp_id INT, facility_eod DATE, hire_status TEXT, trainee_start_date DATE, devstatus_date DATE, status TEXT, status_days INT )AS $$ DECLARE plenddate DATE; plinterval INTERVAL; BEGIN IF venddate IS NULL THEN plenddate = CURRENT_DATE; ELSE plenddate = venddate; END IF; IF vtimespan IS NULL THEN SELECT value::INTERVAL INTO plinterval FROM ntd.site_settings WHERE variable = 'PPT_DATA_YEARS'; IF NOT FOUND THEN plinterval = '10 years'; END IF; ELSE plinterval = vtimespan; END IF; RETURN QUERY WITH t AS ( SELECT DISTINCT td.facility_key, td.traineeid, td.ndc_emp_id, td.facility_eod, td.tia_code AS hire_status, td.trainee_start_date, td.devstatus_date, td.status, td.status_days FROM report.vw_training_details td ), f AS ( SELECT * FROM ntd.facility_dim fd JOIN ( SELECT hft.facility_key, MAX(facility_type_start_date) AS facility_type_start_date FROM ntd.history_facility_type hft GROUP BY hft.facility_key )DT1 USING(facility_key) ) SELECT DT1.facility_key, DT1.facility_code, t.traineeid, t.ndc_emp_id, t.facility_eod, t.hire_status, t.trainee_start_date, t.devstatus_date, t.status, t.status_days FROM t JOIN (SELECT l.facility_key, l.facility_code, CASE WHEN facility_type_start_date IS NOT NULL AND facility_type_start_date > l.last_devstatus_date - plinterval THEN facility_type_start_date ELSE l.last_devstatus_date - plinterval END AS last_devstatus_date FROM (SELECT t.facility_key, f.facility_code, MAX(t.devstatus_date) AS last_devstatus_date FROM t LEFT JOIN f USING(facility_key) WHERE (t.status = 'Completed' OR t.status = 'Did Not Complete') AND t.devstatus_date <= plenddate GROUP BY t.facility_key, f.facility_code )l LEFT JOIN (SELECT pef.facility_key, MAX(facility_type_start_date) AS facility_type_start_date FROM ntd.ppt_exception_facilities pef WHERE facility_type_start_date < CURRENT_DATE GROUP BY pef.facility_key ) e USING(facility_key) )DT1 USING(facility_key) WHERE t.devstatus_date <= plenddate AND t.devstatus_date >= DT1.last_devstatus_date ; END; $$ LANGUAGE plpgsql; This is the first time I have ever encountered a bug and so I have no experience with reporting it. I understand I need to send the underlying table structures but not sure if I do it here or some other way. I do not see an option to attach files. I love Postgres! It is a terrific database and has served me well for about 20 years. -
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Laurenz Albe <laurenz.albe@cybertec.at> — 2025-06-08T00:27:31Z
On Sat, 2025-06-07 at 16:08 +0000, PG Bug reporting form wrote: > PostgreSQL version: 17.5 > Operating system: Red Hat Enterprise Linux release 8.10 (Ootpa) > > A pgsql function that returns Table is relatively simple. It accepts two > arguments; a date and an interval. It uses these to arguments in a query > that returns a result set. This function worked in Postgresql 16.1 and > Postgresql 16.9. However, it does not work in versions 17.0 nor in 17.5 > > [function definition] > > This is the first time I have ever encountered a bug and so I have no > experience with reporting it. Your function essentially is running a query. If you run that query outside of the function, does it finish on time? If not, you have a simpler problem to tackle. It need not necessarily be a bug if some queries perform worse in a later PostgreSQL version. There are too many factors involved in the execution of a complicated query. To give help, we'd need at least the EXPLAIN output from the query and the EXPLAIN (ANALYZE, BUFFERS) output from the query on the old version. Yours, Laurenz Albe
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T01:40:32Z
If I run the query outside of the function it works as expected. But the function never returns. Lowell > On Jun 7, 2025, at 7:27 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Sat, 2025-06-07 at 16:08 +0000, PG Bug reporting form wrote: >> PostgreSQL version: 17.5 >> Operating system: Red Hat Enterprise Linux release 8.10 (Ootpa) >> >> A pgsql function that returns Table is relatively simple. It accepts two >> arguments; a date and an interval. It uses these to arguments in a query >> that returns a result set. This function worked in Postgresql 16.1 and >> Postgresql 16.9. However, it does not work in versions 17.0 nor in 17.5 >> >> [function definition] >> >> This is the first time I have ever encountered a bug and so I have no >> experience with reporting it. > > Your function essentially is running a query. > If you run that query outside of the function, does it finish on time? > If not, you have a simpler problem to tackle. > > It need not necessarily be a bug if some queries perform worse in a later > PostgreSQL version. There are too many factors involved in the execution > of a complicated query. To give help, we'd need at least the EXPLAIN > output from the query and the EXPLAIN (ANALYZE, BUFFERS) output from the > query on the old version. > > Yours, > Laurenz Albe
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T02:07:59Z
Lowell Hought <lowell.hought@gmail.com> writes: > If I run the query outside of the function it works as expected. But the function never returns. This isn't that surprising either: a query inside a function is often translated into a "generic" plan that doesn't depend on specific parameter values, typically sacrificing runtime to avoid repeated planning. Does it get any better if you do "set plan_cache_mode = force_custom_plan" before running the function? regards, tom lane
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T02:57:27Z
Yes! I just changed the parameter as you suggested and the query returned as expected. So I guess something changed between version 16 and version 17? Perhaps the default for that setting? Lowell On Sat, Jun 7, 2025 at 9:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > If I run the query outside of the function it works as expected. But > the function never returns. > > This isn't that surprising either: a query inside a function is > often translated into a "generic" plan that doesn't depend on > specific parameter values, typically sacrificing runtime to > avoid repeated planning. Does it get any better if you do > "set plan_cache_mode = force_custom_plan" before running the > function? > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T02:59:39Z
Wait, I spoke too soon. When I just ran the query I was on version 16. Give me a moment to shut down 16 and fire up 17 and I will try it again. Lowell On Sat, Jun 7, 2025 at 9:57 PM Lowell Hought <lowell.hought@gmail.com> wrote: > Yes! I just changed the parameter as you suggested and the query > returned as expected. > So I guess something changed between version 16 and version 17? Perhaps > the default for that setting? > > Lowell > > On Sat, Jun 7, 2025 at 9:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Lowell Hought <lowell.hought@gmail.com> writes: >> > If I run the query outside of the function it works as expected. But >> the function never returns. >> >> This isn't that surprising either: a query inside a function is >> often translated into a "generic" plan that doesn't depend on >> specific parameter values, typically sacrificing runtime to >> avoid repeated planning. Does it get any better if you do >> "set plan_cache_mode = force_custom_plan" before running the >> function? >> >> regards, tom lane >> >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T03:02:48Z
Lowell Hought <lowell.hought@gmail.com> writes: > Yes! I just changed the parameter as you suggested and the query returned > as expected. > So I guess something changed between version 16 and version 17? Perhaps > the default for that setting? No, that default has not changed. What apparently happened is that cost estimates or something changed enough to persuade the planner to use a generic plan that's considerably inferior to what it was choosing before. There's nowhere near enough info in your report to pin it down more closely than that. Just to eliminate the obvious --- you did run ANALYZE after the upgrade, right? If you had any nondefault settings of default_statistics_target or similar planner parameters, did you make sure they got transferred to the new installation? regards, tom lane
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T03:04:03Z
Changing that parameter had no effect on the version 17 instance. The query itself produces results in a matter of a second or so, but the function still hangs and does not return. Lowell On Sat, Jun 7, 2025 at 9:59 PM Lowell Hought <lowell.hought@gmail.com> wrote: > Wait, I spoke too soon. When I just ran the query I was on version 16. > Give me a moment to shut down 16 and fire up 17 and I will try it again. > > Lowell > > On Sat, Jun 7, 2025 at 9:57 PM Lowell Hought <lowell.hought@gmail.com> > wrote: > >> Yes! I just changed the parameter as you suggested and the query >> returned as expected. >> So I guess something changed between version 16 and version 17? Perhaps >> the default for that setting? >> >> Lowell >> >> On Sat, Jun 7, 2025 at 9:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >>> Lowell Hought <lowell.hought@gmail.com> writes: >>> > If I run the query outside of the function it works as expected. But >>> the function never returns. >>> >>> This isn't that surprising either: a query inside a function is >>> often translated into a "generic" plan that doesn't depend on >>> specific parameter values, typically sacrificing runtime to >>> avoid repeated planning. Does it get any better if you do >>> "set plan_cache_mode = force_custom_plan" before running the >>> function? >>> >>> regards, tom lane >>> >>
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T03:08:06Z
My initial response that changing the parameter worked was mistaken. I forgot to shut down version 16 so the function call happened on that instance. I now shut down 16, fired up 17 and set the parameter, and the behavior did not change. The query itself returns results in about 1 second. The function never returns at all, it just sits there. Lowell On Sat, Jun 7, 2025 at 10:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > Yes! I just changed the parameter as you suggested and the query > returned > > as expected. > > So I guess something changed between version 16 and version 17? Perhaps > > the default for that setting? > > No, that default has not changed. What apparently happened is that > cost estimates or something changed enough to persuade the planner > to use a generic plan that's considerably inferior to what it was > choosing before. There's nowhere near enough info in your report > to pin it down more closely than that. > > Just to eliminate the obvious --- you did run ANALYZE after the > upgrade, right? If you had any nondefault settings of > default_statistics_target or similar planner parameters, > did you make sure they got transferred to the new installation? > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T03:11:11Z
Lowell Hought <lowell.hought@gmail.com> writes: > Changing that parameter had no effect on the version 17 instance. The > query itself produces results in a matter of a second or so, but the > function still hangs and does not return. Darn. But I'm still pretty sure that the problem is an undesirable change of plan, and that we don't have enough information to say more than that. Any chance you could extract a self-contained test case? regards, tom lane
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T03:16:54Z
I can try. I am not sure how to go about that. I did not see on the bug report page where I could upload files, and I am afraid the file size of the tables needed might be too large for email. The entire database when written to an sql dump file is about 20 GB, so not terribly large. I could attempt to dump the schema definition in one file and then the underlying tables in another. Would that work? Or would you also need the files for the function and any views the query relies upon? Lowell On Sat, Jun 7, 2025 at 10:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > Changing that parameter had no effect on the version 17 instance. The > > query itself produces results in a matter of a second or so, but the > > function still hangs and does not return. > > Darn. But I'm still pretty sure that the problem is an undesirable > change of plan, and that we don't have enough information to say > more than that. Any chance you could extract a self-contained > test case? > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T03:26:15Z
Lowell Hought <lowell.hought@gmail.com> writes: > I can try. I am not sure how to go about that. I did not see on the bug > report page where I could upload files, and I am afraid the file size of > the tables needed might be too large for email. No, uploading stuff to that webform doesn't work. But at this point we're just conversing on the pgsql-bugs mailing list, so anything you can squeeze into email is fine. Having said that, nobody likes multi-gigabyte emails. > The entire database when > written to an sql dump file is about 20 GB, so not terribly large. I could > attempt to dump the schema definition in one file and then the underlying > tables in another. Would that work? Or would you also need the files for > the function and any views the query relies upon? Yeah, we'd need all the moving parts. Usually people with this kind of problem don't want to expose their data anyway, for privacy and/or legal reasons. So what I'd suggest is trying to create some little script that generates fake data that's close enough to trigger the problem. Then you just need to provide that script and the DDL and function definitions. regards, tom lane
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-08T03:34:14Z
I will work on that over the next few days. I really appreciate you and your team! Lowell On Sat, Jun 7, 2025 at 10:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > I can try. I am not sure how to go about that. I did not see on the bug > > report page where I could upload files, and I am afraid the file size of > > the tables needed might be too large for email. > > No, uploading stuff to that webform doesn't work. But at this point > we're just conversing on the pgsql-bugs mailing list, so anything you > can squeeze into email is fine. Having said that, nobody likes > multi-gigabyte emails. > > > The entire database when > > written to an sql dump file is about 20 GB, so not terribly large. I > could > > attempt to dump the schema definition in one file and then the underlying > > tables in another. Would that work? Or would you also need the files > for > > the function and any views the query relies upon? > > Yeah, we'd need all the moving parts. > > Usually people with this kind of problem don't want to expose their > data anyway, for privacy and/or legal reasons. So what I'd suggest > is trying to create some little script that generates fake data > that's close enough to trigger the problem. Then you just need to > provide that script and the DDL and function definitions. > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-09T23:35:13Z
I wrote a script to create all of the tables, views, and function in an effort to recreate the issue. I ran the script on both version 16 and version 17 and executed the function on each. On both servers, the function returned results, so the attempt to recreate the problem failed. I then ran both versions of the server simultaneously on different ports and attempted a dump from 16 to version 17. I used the pg_dump from version 17. Once again the restore to version 17 got hung up and did not finish. It hangs at the point where it attempts to REFRESH MATERIALIZED view. The materialized view in question uses the function report.GetReportPoolTrainees that we have been discussing. I deleted the materialized view in the version 16 database and then did a dump/restore to the version 17 database, ran ANALYZE, and attempted to execute the query that the function calls. No luck, it would not return. What is so puzzling to me is that if I do a fresh install of version 16, everything works as it should. But not when I do the exact same thing on version 17. Lowell On Sat, Jun 7, 2025 at 10:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > I can try. I am not sure how to go about that. I did not see on the bug > > report page where I could upload files, and I am afraid the file size of > > the tables needed might be too large for email. > > No, uploading stuff to that webform doesn't work. But at this point > we're just conversing on the pgsql-bugs mailing list, so anything you > can squeeze into email is fine. Having said that, nobody likes > multi-gigabyte emails. > > > The entire database when > > written to an sql dump file is about 20 GB, so not terribly large. I > could > > attempt to dump the schema definition in one file and then the underlying > > tables in another. Would that work? Or would you also need the files > for > > the function and any views the query relies upon? > > Yeah, we'd need all the moving parts. > > Usually people with this kind of problem don't want to expose their > data anyway, for privacy and/or legal reasons. So what I'd suggest > is trying to create some little script that generates fake data > that's close enough to trigger the problem. Then you just need to > provide that script and the DDL and function definitions. > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-14T14:27:19Z
So I tried one more thing. I executed the raw query on version 17 with a LIMIT 1 clause and it returned 1 record. So I increased that to LIMIT 100 and it returned 100 records. I increased to LIMIT 1000 and it returned 1000 records. I increased to 10000 and it returned 10000 records. I increased to 100000 and it returned 19959 records as that is all there are, and it only took a few seconds to return. However, I then removed the LIMIT clause, and once again it was hung and never returned. Why would it return with a LIMIT clause, but not without the LIMIT clause? On Mon, Jun 9, 2025 at 6:35 PM Lowell Hought <lowell.hought@gmail.com> wrote: > I wrote a script to create all of the tables, views, and function in an > effort to recreate the issue. I ran the script on both version 16 and > version 17 and executed the function on each. On both servers, the > function returned results, so the attempt to recreate the problem failed. > I then ran both versions of the server simultaneously on different ports > and attempted a dump from 16 to version 17. I used the pg_dump from > version 17. Once again the restore to version 17 got hung up and did not > finish. It hangs at the point where it attempts to REFRESH MATERIALIZED > view. The materialized view in question uses the > function report.GetReportPoolTrainees that we have been discussing. I > deleted the materialized view in the version 16 database and then did a > dump/restore to the version 17 database, ran ANALYZE, and attempted to > execute the query that the function calls. No luck, it would not return. > > What is so puzzling to me is that if I do a fresh install of version 16, > everything works as it should. But not when I do the exact same thing on > version 17. > > Lowell > > > > > On Sat, Jun 7, 2025 at 10:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Lowell Hought <lowell.hought@gmail.com> writes: >> > I can try. I am not sure how to go about that. I did not see on the >> bug >> > report page where I could upload files, and I am afraid the file size of >> > the tables needed might be too large for email. >> >> No, uploading stuff to that webform doesn't work. But at this point >> we're just conversing on the pgsql-bugs mailing list, so anything you >> can squeeze into email is fine. Having said that, nobody likes >> multi-gigabyte emails. >> >> > The entire database when >> > written to an sql dump file is about 20 GB, so not terribly large. I >> could >> > attempt to dump the schema definition in one file and then the >> underlying >> > tables in another. Would that work? Or would you also need the files >> for >> > the function and any views the query relies upon? >> >> Yeah, we'd need all the moving parts. >> >> Usually people with this kind of problem don't want to expose their >> data anyway, for privacy and/or legal reasons. So what I'd suggest >> is trying to create some little script that generates fake data >> that's close enough to trigger the problem. Then you just need to >> provide that script and the DDL and function definitions. >> >> regards, tom lane >> >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-14T15:15:48Z
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
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-14T15:52:38Z
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@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 > -
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-14T16:12:17Z
Lowell Hought <lowell.hought@gmail.com> writes: > 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. Unfortunately, your email program has mangled the EXPLAIN output to the point of unreadability, as it's preserved neither line breaks nor indentation. Maybe it'd work better to put the output in attachments. regards, tom lane
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Lowell Hought <lowell.hought@gmail.com> — 2025-06-14T21:35:20Z
Attached in three separate files. On Sat, Jun 14, 2025 at 11:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lowell Hought <lowell.hought@gmail.com> writes: > > 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. > > Unfortunately, your email program has mangled the EXPLAIN output to > the point of unreadability, as it's preserved neither line breaks nor > indentation. Maybe it'd work better to put the output in attachments. > > regards, tom lane >
-
Re: BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17
Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-14T22:26:37Z
Lowell Hought <lowell.hought@gmail.com> writes: > Attached in three separate files. Thanks. The only thing that's really obvious here is that the rowcount estimates must be way off, because if the join outputs were as small as the planner thinks, this query couldn't take very long at all. I wonder whether your v17 installation is lacking appropriate statistics. By now autovacuum should have mostly filled that in even if you forgot to see to it when transferring data --- but maybe you had larger-than-default statistics targets in the v16 installation? Or some other change to the default settings? I'd double-check that and then do a manual ANALYZE of the whole database, and see if anything changes. regards, tom lane