On Tue, Feb 21, 2023 at 3:58 AM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Charles (peacech@gmail.com) wrote: > Wrapping the query with a select * from (...) t where length(code) = 4 puts > the execution time back to 27 seconds. > > This is a bit unexpected since I expect that the result from the inner > query to be executed first and then filtered.
It's really not- PG will (correctly) attempt to pull in such subselects into the overall optimization, which is generally better for everyone. If you want to force it, you can use a WITH MATERIALIZED CTE, or throw in an 'OFFSET 0' as a hack into your sub-select, but really it's a much better idea to generate extended stats on what you're filtering as has been suggested, or come up with a better data representation where you're not doing a search on a 'length()' as you are.
Generating extended statistics on the expression (length(code)) causes the planner to generate has join which runs in 183ms (still slower than 37ms on 14.6). Using materialized cte (no extended stats) results in nested loop that runs in 229ms. I think I'll revert back to postgresql 14 for now. Thank you for the suggestions.
As pointed out elsewhere, the query costs seem pretty close and so you’re likely going to get with the bad plan at some point on 14 also. Have you tried tweaking the statistics target? Might help. Though really, as was said before, modeling the data better likely would help a lot.
I have used 14.6 for probably a year running this query multiple times a day (>50x) and have never experienced a slow response time.