Message-ID: <14fa809c-a2b7-4d36-9382-d08a5df4718a@evolu-s.it>
Date: Tue, 19 Nov 2024 11:53:44 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Functions and Indexes
To: pgsql-general@lists.postgresql.org
References: <2e36a2fe-b30f-46a5-937f-3b7d94e75cb6@evolu-s.it>
 <8dfd0bd5-87e5-4de7-9f60-8a680a32bc11@evolu-s.it>
 <61cb94a962667788c9c09107fa9937300e54d3cd.camel@cybertec.at>
Content-Language: en-US, it
From: Moreno Andreo <moreno.andreo@evolu-s.it>
In-Reply-To: <61cb94a962667788c9c09107fa9937300e54d3cd.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/14fa809c-a2b7-4d36-9382-d08a5df4718a%40evolu-s.it>
Precedence: bulk


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.