Re: Functions and Indexes

On 18/11/24 20:05, Laurenz Albe wrote:
> On Mon, 2024-11-18 at 15:37 +0100, Moreno Andreo wrote:
>> I'm creating indexes for some tables, and I came across a doubt.
>>
>> If a column appears in the WHERE clause (and so it should be placed in
>> index), in case it is "processed" in a function (see below), is it
>> possible to insert this function to further narrow down things?
>>
>> Common index:
>> SELECT foo1, foo2 FROM bar WHERE foo1 = 2
>> CREATE index bar1_idx ON bar USING btree(foo1);
>>
>> 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 ... :-)
>
>> Second question: I 've seen contrasting opinions about putting JOIN
>> parameters (ON a.field1 = b.field2) in an index and I'd like to know
>> your thoughts.
> That depends on the join strategy PostgreSQL chooses.
> You can use EXPLAIN to figure out the join strategy.
> This article should explain details:
> https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/
Very nice article, clear and easy to understand!
>
> Yours,
> Laurenz Albe
>
>
Thanks,
Moreno.