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

Hi folks,
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)

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.

Thanks,
Moreno.