Thread: Functions and Indexes
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.
On Mon, 18 Nov 2024 15:37:57 +0100 Moreno Andreo <moreno.andreo@evolu-s.it> wrote: > 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? You probably want to look at https://www.postgresql.org/docs/current/indexes-expressional.html -- Dakkar - <Mobilis in mobile> GPG public key fingerprint = A071 E618 DD2C 5901 9574 6FE2 40EA 9883 7519 3F88 key id = 0x75193F88
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
On 18/11/24 15:43, Gianni Ceccarelli wrote: > On Mon, 18 Nov 2024 15:37:57 +0100 > Moreno Andreo <moreno.andreo@evolu-s.it> wrote: > >> 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? > You probably want to look at > https://www.postgresql.org/docs/current/indexes-expressional.html > Hi Gianni, correct. That's a good starting point (now I have to add complexity since the index should have 4 columns, one of them is referenced with LIKE) Thanks! Moreno
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.
On Tue, 2024-11-19 at 11:53 +0100, Moreno Andreo wrote: > > > 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 ... :-) I don't think "text_pattern_ops" will help here - queries that use LIKE to search for a substring (LIKE '%string%') cannot make use of a b-tree index. Yours, Laurenz Albe
On 19/11/24 12:34, Laurenz Albe wrote:
Oh, OK, i was happy to use BTREEs 'cause I had some issues with GIN/GIST (like indexes way bigger than table and so inefficient). OK, I'll stick with these and try harder to obtain better results.On Tue, 2024-11-19 at 11:53 +0100, Moreno Andreo wrote: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 ... :-)I don't think "text_pattern_ops" will help here - queries that use LIKE to search for a substring (LIKE '%string%') cannot make use of a b-tree index.
One thing I can't understand well.
In https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/
you say
"Note that for inner joins there is no distinction between the join condition and the
WHERE condition, but that doesn't hold for outer joins."What do you mean?
Thanks
Moreno
On Tue, 2024-11-19 at 14:30 +0100, Moreno Andreo wrote: > Inhttps://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/ > you say > "Note that for inner joins there is no distinction between the join condition and the WHERE condition, but that doesn'thold for outer joins." > What do you mean? CREATE TABLE a (id integer); INSERT INTO a VALUES (1), (2), (3); CREATE TABLE b (id integer); INSERT INTO b VALUES (1), (2), (4); SELECT * FROM a JOIN b ON a.id = b.id AND b.id < 2; id │ id ════╪════ 1 │ 1 (1 row) SELECT * FROM a JOIN b ON a.id = b.id WHERE b.id < 2; id │ id ════╪════ 1 │ 1 (1 row) SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.id < 2; id │ id ════╪════ 1 │ 1 2 │ ∅ 3 │ ∅ (3 rows) SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.id < 2; id │ id ════╪════ 1 │ 1 (1 row) Yours, Laurenz Albe