luckyframework / luckyframework/avram
Look in to changing how array.includes() criteria method works
- Dominant language
- Crystal
- Stars
- 183
- Forks
- 67
- PR merge metrics
- No merged PRs in 30d
Description
https://github.com/luckyframework/avram/blob/f84e111a80d475611eba5b1a082ca8d8d8df399e/src/avram/criteria_extensions/includes_criteria.cr#L3-L7
Using this method in a query is handy. If one of your columns is an `Array(?)` column, you can get records where that array contains some value
```crystal
# SELECT * FROM users WHERE 'lucky' = ANY(tags)
UseryQuery.new.tags.includes("lucky")
```
However, I just learned that if you put a `GIN` index on `tags`, postgres won't actually use the index in this way...
Using a GIN index
```
=# CREATE INDEX users_tags_index ON users USING gin (tags);
```
Query as-is now
```
=# EXPLAIN SELECT * FROM users WHERE 'lucky' = ANY(tags);
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..5748.84 rows=208 width=10)
Filter: ('lucky'::text = ANY (tags))
(2 rows)
=# SELECT * FROM users WHERE 'lucky' = ANY(tags);
Time: 8.938 ms
```
But there seems to be an alternate way of doing this query which is a ton faster
```
=# EXPLAIN SELECT * FROM users WHERE tags @> '{"lucky"}';
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=9.61..697.01 rows=208 width=10)
Recheck Cond: (tags @> '{lucky}'::text[])
-> Bitmap Index Scan on users_tags_index (cost=0.00..9.56 rows=208 width=0)
Index Cond: (tags @> '{lucky}'::text[])
(4 rows)
=# SELECT * FROM users WHERE tags @> '{"lucky"}';
Time: 0.533 ms
```
This is a pretty big improvement. Though, I should note that without an index, the first way queries twice as fast as the second way...
```
=# SELECT * FROM users WHERE 'lucky' = ANY(tags);
Time: 10.897 ms
=# SELECT * FROM users WHERE tags @> '{"lucky"}';
Time: 23.410 ms
```
Contributor guide
Research direction
Start with src/avram/criteria_extensions/includes_criteria.cr at the linked lines and inspect how the includes criteria builds its SQL. Compare the existing ANY query with PostgreSQL's array-containment query and GIN-index behavior shown in the issue. Done should include a decided query strategy that improves indexed array lookups while accounting for the reported no-index tradeoff.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- crystal, postgresql
- Domain
- database
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100