Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDOJa-000Nty-9h for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 13:30:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tDOJY-00DOK4-Dk for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 13:30:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDOJX-00DOJw-V4 for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 13:30:44 +0000 Received: from mail.evolu-s.it ([77.81.232.174]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDOJU-002h4G-VL for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 13:30:43 +0000 dkim-signature: v=1; a=rsa-sha256; d=evolu-s.it; s=dkim; c=relaxed/relaxed; q=dns/txt; h=From:Subject:Date:Message-ID:To:MIME-Version:Content-Type:In-Reply-To:References; bh=jPHDHaBKsrdMl9D5S+IqUCbdkEUuTVMpPuuBnk3Mrz8=; b=HM7rEiAGtGMEV5hnDm82gdbLZeU5K9Hur3nKbIkdoDwB1EMkyLAe+4YFXYJ+RU032XPvnoNi0VrZkpwyk9FZasteAa5hKjHSQbnfpE1A1xeEP6WkcVIdSkJDTKiwmx4se6/epRSsvIKSsoUpJoN4kX/sdEpZmr1hT7sxLAdb4Ss= Received: from [192.168.1.103] (res-129401d.ppp.twt.it [83.217.179.105]) by mail.evolu-s.it with ESMTPSA (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128) ; Tue, 19 Nov 2024 14:30:37 +0100 Content-Type: multipart/alternative; boundary="------------Y2RkP6mo0lr3oyJNtkMqwZET" Message-ID: <92aa9401-e44d-4a45-9851-0617935e06a2@evolu-s.it> Date: Tue, 19 Nov 2024 14:30:37 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Functions and Indexes To: pgsql-general@lists.postgresql.org References: <2e36a2fe-b30f-46a5-937f-3b7d94e75cb6@evolu-s.it> <8dfd0bd5-87e5-4de7-9f60-8a680a32bc11@evolu-s.it> <61cb94a962667788c9c09107fa9937300e54d3cd.camel@cybertec.at> <14fa809c-a2b7-4d36-9382-d08a5df4718a@evolu-s.it> Content-Language: en-US, it From: Moreno Andreo In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Y2RkP6mo0lr3oyJNtkMqwZET Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------Y2RkP6mo0lr3oyJNtkMqwZET Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

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 be=
comes
SELECT foo1, foo2 FROM bar WHERE (POSITION(foo1 IN 'blah blah') >0)

You could create an index like

=C2=A0=C2=A0=C2=A0 CREATE INDEX ON bar (position(foo1 IN 'blah blah'));

Alternatively, you could have a partial index:

=C2=A0=C2=A0=C2=A0 CREATE INDEX ON bar (foo1) INCLUDE (foo2)
=C2=A0=C2=A0=C2=A0 WHERE position(foo1 IN 'blah blah') > 0;

Interesting. Never seen this form, I'll look further on it.

I stumbled into
https://www.cybertec-postgresq=
l.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=20
WHERE condition with a combination of LIKE and the POSITION expression=20
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.cybe= rtec-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=C2=A0WHERE=C2=A0= condition, but that doesn't hold for outer joins."
What do you mean?

Thanks
Moreno
--------------Y2RkP6mo0lr3oyJNtkMqwZET--