Last night I was working on a PostgreSQL function which would take a bunch of rows from one table and update rows in another table. This is part of the FreshPorts packages project.
When I ran it from the command line, it worked as expected. When I ran it from within a script, the DELETE part of the function took about 3 minutes, instead of 300ms.
Early this morning as I was waking up, I dozed and thought about writing to the PostgreSQL mailing lists about this. I wasn’t getting anywhere.
Here is the email I composed earlier today:
Hello,
Should 'analyse table1' work the same from within a function and from psql?
I'm using PostgreSQL 12.2
I have a delete which runs much faster if I do an ANALYSE before the DELETE.
Without the ANALYSE, it runs in 3 minutes. With, in 300 ms.
The DELETE in question:
https://explain.depesz.com/s/pPxrI
The problem: that ANALYSE doesn't help me from within a function.
There is an UPDATE, just before the DELETE, which affects 28,000 rows in packages_raw.
It sets values in the fields used in the DELETE.
That works, manually, via psql, but doesn't help within the function I'm trying to write.
The commands I'm running manually at https://gist.github.com/dlangille/fe93a1e0c521d06a7873b18f8b0fb0d8
The function is at: https://gist.github.com/dlangille/9414787620447342daa4d78753f658b4
—
Dan Langille
http://langille.org/
I had completed this email and went back and added in lines 3 and 5. When typing line 3, I thought… maybe I should check the analyse documentation and see if functions are mentioned.
I’m glad I did:
To analyze a table, one must ordinarily be the table’s owner or a superuser.
That’s it! The script runs as a different user from what I was testing with.
I changed the table owner:
freshports.dev=# alter table packages_raw owner to packager_dev; ALTER TABLE
Now we get speed. Now it works as expected.
The lessons
- Compose good emails when asking for help
- Indicate the tools and versions you are using
- Read the documentation one last time before sending
Hope this helps.
