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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com> writes:
> > On Wed, Sep 15, 2021 at 3:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(dot)mustkivi(at)gmail(dot)com