public inbox for [email protected]
help / color / mirror / Atom feedRe: Functions and Indexes
3+ messages / 2 participants
[nested] [flat]
* Re: Functions and Indexes
@ 2024-11-19 11:34 Laurenz Albe <[email protected]>
2024-11-19 13:30 ` Re: Functions and Indexes Moreno Andreo <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2024-11-19 11:34 UTC (permalink / raw)
To: Moreno Andreo <[email protected]>; [email protected]
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] 3+ messages in thread
* Re: Functions and Indexes
2024-11-19 11:34 Re: Functions and Indexes Laurenz Albe <[email protected]>
@ 2024-11-19 13:30 ` Moreno Andreo <[email protected]>
2024-11-19 17:44 ` Re: Functions and Indexes Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Moreno Andreo @ 2024-11-19 13:30 UTC (permalink / raw)
To: [email protected]
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] 3+ messages in thread
* Re: Functions and Indexes
2024-11-19 11:34 Re: Functions and Indexes Laurenz Albe <[email protected]>
2024-11-19 13:30 ` Re: Functions and Indexes Moreno Andreo <[email protected]>
@ 2024-11-19 17:44 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2024-11-19 17:44 UTC (permalink / raw)
To: Moreno Andreo <[email protected]>; [email protected]
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] 3+ messages in thread
end of thread, other threads:[~2024-11-19 17:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-19 11:34 Re: Functions and Indexes Laurenz Albe <[email protected]>
2024-11-19 13:30 ` Moreno Andreo <[email protected]>
2024-11-19 17:44 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox