Re: postrgesql query planner wrong desicion

[Kenny Bachman <kenny.bachman17@xxxxxxxxx>]

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.janes@xxxxxxxxx>, 19 Haz 2022 Paz, 23:33 tarihinde şunu yazdı:

On Sat, Jun 18, 2022 at 2:42 AM Kenny Bachman <kenny.bachman17@xxxxxxxxx> 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