Re: Functions and Indexes

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.

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.

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