pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions and Indexes
8+ messages / 3 participants
[nested] [flat]
* Functions and Indexes
@ 2024-11-18 14:37 Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 14:43 ` Re: Functions and Indexes Gianni Ceccarelli <dakkar@thenautilus.net>
2024-11-18 19:05 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 8+ messages in thread
From: Moreno Andreo @ 2024-11-18 14:37 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
@ 2024-11-18 14:43 ` Gianni Ceccarelli <dakkar@thenautilus.net>
2024-11-19 08:42 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
1 sibling, 1 reply; 8+ messages in thread
From: Gianni Ceccarelli @ 2024-11-18 14:43 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 14:43 ` Re: Functions and Indexes Gianni Ceccarelli <dakkar@thenautilus.net>
@ 2024-11-19 08:42 ` Moreno Andreo <moreno.andreo@evolu-s.it>
0 siblings, 0 replies; 8+ messages in thread
From: Moreno Andreo @ 2024-11-19 08:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
@ 2024-11-18 19:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 10:53 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2024-11-18 19:05 UTC (permalink / raw)
To: Moreno Andreo <moreno.andreo@evolu-s.it>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 19:05 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-11-19 10:53 ` Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 11:34 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Moreno Andreo @ 2024-11-19 10:53 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 19:05 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 10:53 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
@ 2024-11-19 11:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 13:30 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2024-11-19 11:34 UTC (permalink / raw)
To: Moreno Andreo <moreno.andreo@evolu-s.it>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 19:05 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 10:53 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 11:34 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-11-19 13:30 ` Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 17:44 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Moreno Andreo @ 2024-11-19 13:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 19/11/24 12:34, Laurenz Albe wrote:
> 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.
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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions and Indexes
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 19:05 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 10:53 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 11:34 ` Re: Functions and Indexes Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 13:30 ` Re: Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
@ 2024-11-19 17:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2024-11-19 17:44 UTC (permalink / raw)
To: Moreno Andreo <moreno.andreo@evolu-s.it>; pgsql-general@lists.postgresql.org
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't hold 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-11-19 17:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-18 14:37 Functions and Indexes Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 14:43 ` Gianni Ceccarelli <dakkar@thenautilus.net>
2024-11-19 08:42 ` Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-18 19:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 10:53 ` Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 11:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-11-19 13:30 ` Moreno Andreo <moreno.andreo@evolu-s.it>
2024-11-19 17:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox