Re: your mail

Hi,

On Sat, Jul 02, 2022 at 03:15:55PM +0300, Hung Nguyen wrote:
> Hellom
>
> <https://dba.stackexchange.com/posts/314015/timeline>
>
> I've just upgraded my postgres instance from v11 to v14. There was an
> interesting problem because we have a trigram index on order_id column.
>
> This new feature makes our simple join query on that column very slow. For
> example:
>
> SELECT count(*) from order_rows o1 join order o2 on o1.order_id = o2.order_id

Oh, that's surprising.  It's not clear to me why any index would be used with
such a query, especially if it's not compatible with index only scans.  Is that
some simplification of some query or really one that exhibit the problem?

>
> To solve this problem the existing trigram index must be dropped and we
> cannot use ILIKE queries on this column. I just wonder if there is any way
> to tell postgres what index (in this case btree index) to use when doing
> the join operations?

There's no such capability builtin.  However, trigram indexes should be way
more expensive that btree indexes in general, so the planner should be able to
make the correct decision, there must be something else going on.
>
> [Postgres 14] Allow GiST/GIN pg_trgm indexes to do equality lookups (Julien
> Rouhaud)
>
> I'm not sure if this is really a bug, but its' super weird if the query
> planner favors the trigram index over the b-tree index for joining is not
> optimal to me. Thank you so much.

Can you provide the full definition for both order and order_rows, and EXPLAIN
(ANALYZE, TIMING, BUFFERS) for the problematic query, with and without the trgm
index being used?  Doing a "SET enable_bitmapscan = 0;" should be enough for
that.  Do you have any usual settings configured, like enable_* or others?