|
On 19/11/24 12:34, Laurenz Albe wrote:
Oh, OK, i was happy to use BTREEs 'cause I had some issues with GIN/GIST (like indexes way bigger than table and so inefficient). OK, I'll stick with these and try harder to obtain better results.On Tue, 2024-11-19 at 11:53 +0100, Moreno Andreo wrote:What about if query becomes SELECT foo1, foo2 FROM bar WHERE (POSITION(foo1 IN 'blah blah') >0)You could create an index like CREATE INDEX ON bar (position(foo1 IN 'blah blah')); Alternatively, you could have a partial index: CREATE INDEX ON bar (foo1) INCLUDE (foo2) WHERE position(foo1 IN 'blah blah') > 0;Interesting. Never seen this form, I'll look further on it. I stumbled into https://www.cybertec-postgresql.com/en/indexing-like-postgresql-oracle/ and discovered text_pattern_ops. I'm wondering if it can be of any use in my index, that should hold a WHERE condition with a combination of LIKE and the POSITION _expression_ above. More docs to read ... :-)I don't think "text_pattern_ops" will help here - queries that use LIKE to search for a substring (LIKE '%string%') cannot make use of a b-tree index. One thing I can't understand well. In https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/ you say "Note that for inner joins there is no distinction between the join condition and the WHERE condition,
but that doesn't hold for outer joins."What do you mean? Thanks Moreno |
