I have a rather structurally "simple" query that consists basically in these clauses:
[650 columns here]
(Some conditions in MAIN_TABLE plus some others)
As someone can tell, the column projection is rather large.
When I say the query is "simple", I mean it has few (3) sub-queries in
SELECT clause that are pretty straightforward. Other than that, it doesn't have CTE's, sub-select's in Join clauses, grouping, sorting and so on.
This query is bouncing between 20s-25s to complete.
Now, if I randomly start pulling some of these columns off, at some point, the query performance improves sudden and significantly (from 20s to 1-2s).
If I export the database (which is small now, new deployment, ~1Gb size total) and run the same query on my dev machine, it runs even faster: 0.5s
I suppose, to some point, the engine must use some disk buffer as intermediate storage before fetching the results. What bugs me, is that this column selection modification changes the inner most performance of the query plan (that is, the query start tables per se) so much for no apparent reason.
How can I correctly diagnose what's going on and tackle this issue - any advice?
- PostgreSQL Version 14.5
- OS: Rocky Linux 9.0 (Blue Onyx)
- Parameters are followed based pgTune suggestion - the server has much more CPU/RAM than needed by now
- I've already set the WORK_MEM parameter to a large value (100x the original) for a session, same result.