Understood. Thank you so much for looking into this! Best regards, Kristjan On Wed, Sep 15, 2021 at 5:34 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Kristjan Mustkivi <sonicmonkey@xxxxxxxxx> writes: > > On Wed, Sep 15, 2021 at 3:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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: kristjan.mustkivi@xxxxxxxxx
