public inbox for [email protected]
help / color / mirror / Atom feedRe: Functions and Indexes
2+ messages / 2 participants
[nested] [flat]
* Re: Functions and Indexes
@ 2024-11-18 19:05 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Laurenz Albe @ 2024-11-18 19:05 UTC (permalink / raw)
To: Moreno Andreo <[email protected]>; [email protected]
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] 2+ messages in thread
* Re: Functions and Indexes
@ 2024-11-19 10:53 Moreno Andreo <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Moreno Andreo @ 2024-11-19 10:53 UTC (permalink / raw)
To: [email protected]
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] 2+ messages in thread
end of thread, other threads:[~2024-11-19 10:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-18 19:05 Re: Functions and Indexes Laurenz Albe <[email protected]>
2024-11-19 10:53 ` Moreno Andreo <[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