Postgresql array function doesn't use index

Question

I've created a postgresql array column with a GIN index, and I'm trying to do a contains query on that column. With standard postgresql I can get that working correctly like this:

SELECT d.name 
FROM deck d 
WHERE d.card_names_array @> string_to_array('"Anger#2","Pingle Who Annoys#2"', ',') 
LIMIT 20;

Explain analyze:

Limit  (cost=1720.01..1724.02 rows=1 width=31) (actual time=7.787..7.787 rows=0 loops=1)
  ->  Bitmap Heap Scan on deck deck0_  (cost=1720.01..1724.02 rows=1 width=31) (actual time=7.787..7.787 rows=0 loops=1)
        Recheck Cond: (card_names_array @> '{"\"Anger#2\"","\"Pingle Who Annoys#2\""}'::text[])
        ->  Bitmap Index Scan on deck_card_names_array_idx  (cost=0.00..1720.01 rows=1 width=0) (actual time=7.785..7.785 rows=0 loops=1)
              Index Cond: (card_names_array @> '{"\"Anger#2\"","\"Pingle Who Annoys#2\""}'::text[])
Planning time: 0.216 ms
Execution time: 7.810 ms

Unfortunately (in this instance) I'm using QueryDSL with which I read the native array functions like @> are impossible to use. However, this answer says you can use arraycontains instead to do the same thing. I got that working, and it returns the correct results, but it doesn't use my index.

SELECT d.name 
FROM deck d 
WHERE arraycontains(d.card_names_array, string_to_array('"Anger#2","Pingle Who Annoys#2"', ','))=true
LIMIT 20;

Explain analyze:

Limit  (cost=0.00..18.83 rows=20 width=31) (actual time=1036.151..1036.151 rows=0 loops=1)
  ->  Seq Scan on deck deck0_  (cost=0.00..159065.60 rows=168976 width=31) (actual time=1036.150..1036.150 rows=0 loops=1)
        Filter: arraycontains(card_names_array, '{"\"Anger#2\"","\"Pingle Who Annoys#2\""}'::text[])
        Rows Removed by Filter: 584014
Planning time: 0.204 ms
Execution time: 1036.166 ms

This is my QueryDSL code to create the boolean expression:

predicate.and(Expressions.booleanTemplate(
        "arraycontains({0}, string_to_array({1}, ','))=true",
        deckQ.cardNamesArray,
        filters.cards.joinToString(",") { "${it.cardName}#${it.quantity}" }
))

Is there some way to get it to use my index? Or maybe a different way to do this with QueryDSL to use the native @> function?

Not really an answer, but for my use case I realized I could just index a single string with all the values in it and do a like query with %Anger#2%. It is sufficiently performant for my use case, and means I don't have to jump out of my querydsl sandbox. — Nelaina
– Nelaina, Commented Feb 12, 2019 at 0:04

Akhil Bansal · Accepted Answer · 2024-02-15 18:02:30Z

We had a very similar issue. Unfortunately arraycontains does not use the index and sequentially scans the table.

The alternate way that worked for us was to extend eclipselink to add a custom operator for @>. We used querydsl to invoke the custom operator.

  public static final int ArrayContainsOperator = 52170;
  public static final String ArrayContainsOperatorStr = "array_contains";
  public static final ExpressionOperator ARRAY_CONTAINS_OPERATOR = new ExpressionOperator() {
    {
      setSelector(ArrayContainsOperator);
      setName(ArrayContainsOperatorStr);
      this.setType(ExpressionOperator.FunctionOperator);
      Vector v = NonSynchronizedVector.newInstance(3);
      v.add("");
      v.add(" @> ARRAY[");
      v.add("]");
      this.printsAs(v);
      this.bePrefix();
      this.setNodeClass(FunctionExpression.class);
    }
  };
  ExpressionOperator.registerOperator(ArrayContainsOperator, ArrayContainsOperatorStr);
  ExpressionOperator.addOperator(ARRAY_CONTAINS_OPERATOR);

Once the operator is added, it can be invoked from querydsl like following -

Expressions.booleanTemplate("Operator('" + ArrayContainsOperatorStr + "', {0}, {1})", listPath, value);

Collectives™ on Stack Overflow

Postgresql array function doesn't use index

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