Re: Postgres chooses slow query plan from time to time
Understood. Thank you so much for looking into this! Best regards, Kristjan On Wed, Sep 15, 2021 at 5:34 PM Tom Lane wrote: > > Kristjan Mustkivi writes: > > On Wed, Sep 15, 2021 at 3:16 PM Tom Lane wrote: > >> Note the lack of any visible cast on the varchar column, in each one of > >> these queries, even where I tried to force one to appear. There is > >> something happening in your database that is not happening in mine. > > > The following extensions have been installed: > > [ nothing very exciting ] > > I still get the same results after installing those extensions. > > I realized that the reason I don't see a cast is that > fix_indexqual_operand removes the cast from an index qualifier > expression's index-column side. In any other context, there would > be a cast there, since the operator is =(text,text) not > =(varchar,varchar). So that seems like a red herring ... or is it? > Now I'm confused by your original report, in which you show > > >>> -> Index Scan using mytable_pk on mytable pbh (cost=0.70..176.82 > >>> rows=186 width=66) (actual time=1.001..8.610 rows=25 loops=1) > >>> Index Cond: ((cage_code = 123) AND (cage_player_id = > >>> '12345'::bigint) AND ((product_code)::text = 'PRODUCT'::text) AND > >>> ((balance_type)::text = 'TOTAL'::text)) > >>> Filter: (modified_time < '2021-09-13 > >>> 04:00:00+00'::timestamp with time zone) > > According to the code I just looked at, there should absolutely not > be casts on the product_code and balance_type index columns here. > So I'm not quite sure what's up ... -ENOCAFFEINE perhaps. > > Nonetheless, this point is probably just a sideshow. The above > EXPLAIN output proves that the planner *can* match this index, > which destroys my idea that you had a datatype mismatch preventing > it from doing so. > > After looking again at the original problem, I think you are getting > bit by an issue we've seen before. The planner is coming out with > a decently accurate cost estimate for the query when specific values > are inserted for the parameters. However, when it considers a generic > version of the query with no known parameter values, the cost estimates > are not so good, and by chance it comes out estimating a very low cost > for the alternative plan that uses the other index. That cost is not > right, but the planner doesn't know that, so it seizes on that plan. > > This is a hard problem to fix, and we don't have a good answer for it. > In v12 and up, you can use the big hammer of disabling generic plans by > setting plan_cache_mode to "force_custom_plan", but v11 doesn't have > that parameter. You might need to avoid using a prepared statement for > this query. > > regards, tom lane -- Kristjan Mustkivi Email: [email protected]
Want function to be called only once in query
I have a PL/pgSQL function that I want to call within a query, but the function is fairly expensive to execute so I only want it executed once within the query. However the planner seems to reorganize my query so that it calls the function for every row. We were previously on Pg 9.6 and this wasn't a problem then. But now that we have upgraded to Pg 13, the behaviour has changed. I thought that marking the function as STABLE would mean that the function would only be called once within a query, but this doesn't seem to be the case. (Note: the function isn't IMMUTABLE). I've also tried increasing the cost of the function, but this doesn't make any difference. >From looking at previous posts I discovered that putting "offset 0" on the function call in a "with" clause means that it only gets called once (because then the Common Table Expression isn't combined with the rest of the query). This does work, however it seems rather a kludge (and might not work in future versions of PostgreSQL). There must be a "proper" way to get the planner to call a function only once. Postgres version: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit Here's a simple test case that demonstrates the issue: create or replace function test_caching(v integer) returns text as $BODY$ begin raise NOTICE 'In test_caching(%) function', v; return 'Test'; end $BODY$ LANGUAGE plpgsql STABLE COST 500; select n, test_caching(7) from generate_series(1, 10) n; -- test_caching(...) is called 10 times with tc as ( select test_caching(7) ) select n, tc.test_caching from tc cross join generate_series(1, 10) n; -- test_caching(...) is called 10 times -- (in Pg 9.6, test_caching(...) is only called once) with tc as ( select test_caching(7) offset 0 ) select n, tc.test_caching from tc cross join generate_series(1, 10) n; -- test_caching(...) is called once -- works, but a kludge Steve -- Steve Pritchard Database Developer British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030 Registered Charity No 216652 (England & Wales) No SC039193 (Scotland) Company Limited by Guarantee No 357284 (England & Wales)
Re: Want function to be called only once in query
On Thu, Sep 16, 2021 at 4:51 AM Steve Pritchard wrote: > > I have a PL/pgSQL function that I want to call within a query, but the > function is fairly expensive to execute so I only want it executed once > within the query. However the planner seems to reorganize my query so that it > calls the function for every row. > > We were previously on Pg 9.6 and this wasn't a problem then. But now that we > have upgraded to Pg 13, the behaviour has changed. > The behavior for planning a CTE changed in PG12. > There must be a "proper" way to get the planner to call a function only once. > Add the MATERIALIZED keyword to the WITH statement
Re: Want function to be called only once in query
> Add the MATERIALIZED keyword to the WITH statement Many thanks Jim, that's just what I needed - that does the trick. It's hard to keep abreast of these SQL changes. Thank goodness for mailing lists! Steve On Thu, 16 Sept 2021 at 11:56, Jim Mlodgenski wrote: > On Thu, Sep 16, 2021 at 4:51 AM Steve Pritchard > wrote: > > > > I have a PL/pgSQL function that I want to call within a query, but the > function is fairly expensive to execute so I only want it executed once > within the query. However the planner seems to reorganize my query so that > it calls the function for every row. > > > > We were previously on Pg 9.6 and this wasn't a problem then. But now > that we have upgraded to Pg 13, the behaviour has changed. > > > > The behavior for planning a CTE changed in PG12. > > > There must be a "proper" way to get the planner to call a function only > once. > > > Add the MATERIALIZED keyword to the WITH statement > -- Steve Pritchard Database Developer British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030 Registered Charity No 216652 (England & Wales) No SC039193 (Scotland) Company Limited by Guarantee No 357284 (England & Wales)
