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?