luckyframework / luckyframework/avram

Look in to changing how array.includes() criteria method works

Open
#968 3 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.