[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Request to help on GIS Query improvement suggestion.
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-05-22 14:14:29
Message-ID: CAHOFxGrhV7H9gqGnTqRJauVh__q=EGKCkC_RVqbamG49x_Ppcw () mail ! gmail ! com
[Download RAW message or body]

Your indexes and operators are not compatible. You have added a btree index
on md5 function result and are not using md5 in your query, and also using
LIKE operator not one of the supported ones. I believe it might use a btree
operator (plain value, not md5 result) if you are always searching for
"string starts with ____ but I don't know what it ends with" but you can't
possibly use a btree index where you are putting a wild card at the front.

https://www.postgresql.org/docs/9.5/indexes-types.html

a gist index operators supported-
https://www.postgresql.org/docs/9.5/gist-builtin-opclasses.html

Here's a whole page on full text search, it would be worth a read-
https://www.postgresql.org/docs/9.5/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX

[Attachment #3 (text/html)]

<div dir="ltr">Your indexes and operators are not compatible. You have added a btree \
index on md5 function result and are not using md5 in your query, and also using LIKE \
operator not one of the supported ones. I believe it might use a btree operator \
(plain value, not md5 result) if you are always searching for &quot;string starts \
with ____ but I don&#39;t know what it ends with&quot; but you can&#39;t possibly use \
a btree index where you are putting a wild card at the front.<div><br></div><div><a \
href="https://www.postgresql.org/docs/9.5/indexes-types.html">https://www.postgresql.org/docs/9.5/indexes-types.html</a><br></div><div><br></div><div>a \
gist index operators supported-</div><div><a \
href="https://www.postgresql.org/docs/9.5/gist-builtin-opclasses.html">https://www.pos \
tgresql.org/docs/9.5/gist-builtin-opclasses.html</a></div><div><br></div><div>Here&#39;s \
a whole page on full text search, it would be worth a read-<div><a \
href="https://www.postgresql.org/docs/9.5/textsearch-tables.html#TEXTSEARCH-TABLES-IND \
EX">https://www.postgresql.org/docs/9.5/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX</a><br></div></div></div>




[prev in list] [next in list] [prev in thread] [next in thread]