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.
