Message-ID: <61cb94a962667788c9c09107fa9937300e54d3cd.camel@cybertec.at>
Subject: Re: Functions and Indexes
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Moreno Andreo <moreno.andreo@evolu-s.it>, 
	pgsql-general@lists.postgresql.org
Date: Mon, 18 Nov 2024 20:05:31 +0100
In-Reply-To: <8dfd0bd5-87e5-4de7-9f60-8a680a32bc11@evolu-s.it>
References: <2e36a2fe-b30f-46a5-937f-3b7d94e75cb6@evolu-s.it>
	 <8dfd0bd5-87e5-4de7-9f60-8a680a32bc11@evolu-s.it>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/61cb94a962667788c9c09107fa9937300e54d3cd.camel%40cybertec.at>
Precedence: bulk

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.
>=20
> If a column appears in the WHERE clause (and so it should be placed in=
=20
> index), in case it is "processed" in a function (see below), is it=20
> possible to insert this function to further narrow down things?
>=20
> Common index:
> SELECT foo1, foo2 FROM bar WHERE foo1 =3D 2
> CREATE index bar1_idx ON bar USING btree(foo1);
>=20
> 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;

> Second question: I 've seen contrasting opinions about putting JOIN=20
> parameters (ON a.field1 =3D b.field2) in an index and I'd like to know=
=20
> 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-p=
ostgresql/

Yours,
Laurenz Albe