On Fri, 4 Dec 2020 at 15:39, Marco Colli wrote:
> Hello!
>
> We have a multi-tenant service where each customer has millions of users
> (total: ~150M rows). Now we would like to let each customer define some
> custom columns for his users and then let the customer search his users
> efficiently b
On Fri, 4 Feb 2022 at 09:11, Les wrote:
|
> -> Index Only Scan using oo_file_idx_relpath on media.oo_file f2
> (cost=0.55..108499.27 rows=5381 width=0) (actual time=564.756..564.756 rows=0
> loops=45)|
> Filter:
On Fri, 4 Feb 2022 at 10:09, Les wrote:
>
> Oh I see, the query planner does not know that there will be no % characters
> in file and folder names.
>
> But what is the solution then? It just seems wrong that I can speed up a
> query 1000 times by replacing it with a nested loop in a pl/sql func
On Fri, 4 Feb 2022 at 12:27, Les wrote:
> PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan for
> A, I, C letters (with the "like" query).
That's interesting.
Does it help if you create an additional index on relpath with the
text_pattern_ops modifier, e.g.
CREATE INDEX
On Fri, 4 Feb 2022 at 13:07, Les wrote:
>
>>
>> > PostgreSQL uses seq scan for O, F, N, T letters, but it uses index scan
>> > for A, I, C letters (with the "like" query).
>>
>> That's interesting.
>>
>> Does it help if you create an additional index on relpath with the
>> text_pattern_ops modifi
On Fri, 4 Feb 2022 at 13:21, Les wrote:
>
>> What if you try applying the C collation to the values from the table:
>>
>> where fi.is_active and fi.relpath collate "C" ^@ 'A'
>
>
> Slow
What about this:
fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")
On Fri, 4 Feb 2022 at 14:07, Les wrote:
>
>
>
>> > Slow
>>
>> What about this:
>>
>> fi.relpath between ('A' collate "C") and ('A'||chr(255) collate "C")
>
>
> It uses index scan.
> Although the same with 'Természettudomány' uses seq scan:
>
>
> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
> select
