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; > 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/ Yours, Laurenz Albe
