Re: postrgesql query planner wrong desicion

Hi Jeff,

Thank you so much for your explanation. I realized that the gist index was
used many many times. So, I couldn't drop the gist index permanently. My
indexes definitions for the "Code" varchar column, are below:

"Pool_Party_Code_gist" gist ("Code")
"Pool_Party_Code_idx" btree ("Code")
"Pool_Party_Code_idx1" gist ("Code" gist_trgm_ops)

The B-tree index is not used by the planner for equality queries. It uses
the gist index. I did REINDEX, VACUUM, and ANALYZE for the table and all
indexes, but the result did not change. For a basic example;

EXPLAIN ANALYZE SELECT * FROM dsi."Pool_Party" where "Code" =
'TEAM-FIXPOWERUSER';
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30
rows=1 width=502) (actual time=0.485..0.550 rows=1 loops=1)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Planning Time: 0.823 ms
Execution Time: 0.586 ms

Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, 19 Haz 2022 Paz, 23:33 tarihinde şunu
yazdı:

> On Sat, Jun 18, 2022 at 2:42 AM Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
> wrote:
>
>> Hi Tom,
>>
>> The gist index is used by other queries with LIKE or ILIKE operators.
>> Should I drop the gist index for text or varchar columns?
>>
>
> This story doesn't make sense to me. The gist operator for text provided
> by btree_gist does not support LIKE (other than in the same way btree
> indexing does), so there is no point in making one of those indexes for
> this purpose. And the gist operator for text provided by pg_trgm does not
> support equality (until PostgreSQL v14) so that type of index would not
> "capture" the equality comparison in v12.11. If not one of those two, then
> where are you getting your gist operator class from?
>
> That is not to say the costing of GiST indexes shouldn't be improved, but
> I don't see how it could sensibly be causing this problem under v12.
>
> Cheers,
>
> Jeff
>