Postgres Functional Index on string_to_array(long_string, ',') not being used

Question

I'm dealing with a ~20M line table in Postgres 10.9 that has a text column in that is a bunch of comma delimited strings. This table gets joined all over to many tables that are much longer, and every time the previous authors did so, they join with the on clause being some_other_string = Any(string_to_array(col, ',')) I'm trying to implement a quick optimization to make queries faster while I work on a better solution with the following index:

My functional index: create index string_to_array_index on happy_table (string_to_array(col));

Test query: select string_to_array(col, ',') from happy_table;

When I execute an explain on the test query in order to see if the index is being used, I can see that it isn't. I see examples of functional indexes on strings where they lowercase the string or perform some basic operation like that. Do functional indexes work with string_to_array?

select a.id
from joyful_table a
  join happy_table b on a.col = any(string_to_array(b.col, ','));

@a_horse_with_no_name my bad. I added an adjusted query at the bottom. — mj_
– mj_, Commented Feb 24, 2020 at 22:08

Laurenz Albe · Accepted Answer · 2020-02-25 07:34:50Z

2

That is a bad design. No matter what you do and how big the tables are, you are stuck with a nested loop join (because the join condition does not use the = operator).

You are right; the best you can do is to speed up that nested loop with an index.

Your index doesn't work because it is a B-tree index, and that cannot be used with arrays in a meaningful way. What you need is a GIN index:

CREATE INDEX ON happy_table USING gin (string_to_array(col, ','));

But that index won't be used with = ANY. You'll have to rewrite the join to

SELECT a.id
FROM joyful_table a
   JOIN happy_table b
      ON ARRAY[a.col] <@ string_to_array(b.col, ',');

answered Feb 25, 2020 at 7:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Functional Index on string_to_array(long_string, ',') not being used

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related