I'm using Postgres 15 and have a ledger-like table with over 10 million rows. I'm trying to test out adding an array column for storing tags. Here is how the ledger table looks:
| id | user_id | amount | tags | init_date |
|---|---|---|---|---|
| 1 | 112233 | 100 | {market, tag1, tag2} | 2023-03-11 02:58:17 |
| 2 | 112233 | 20 | {market, tag1, tag2} | 2023-02-11 00:00:00 |
| 3 | 112233 | 200 | {gas, tag1, tag2} | 2023-01-11 15:59:26 |
| 4 | 112233 | 40 | {cinema, tag1, tag2} | 2023-01-10 22:37:53 |
The ledger table already has these indexes defined:
CREATE INDEX ledger_init_date_idx ON schema.ledger USING btree (init_date)
CREATE INDEX ledger_user_id_idx ON schema.ledger USING btree (user_id)
And I have a second table where I have my own definition (for flexibility) of the tags that define a category. We ignore the rest of the tags like tag1 and tag2, those are not category tags and are irrelevant for this discussion. Here is how the tag_category table looks:
| id | tag | category |
|---|---|---|
| 1 | market | groceries |
| 2 | gas | transportation |
| 3 | cinema | entertainment |
I'm trying out indexes that can be added for running a select query for summing up the user's amounts by category. This is the query's result:
| total | category |
|---|---|
| 120 | groceries |
| 200 | transportation |
| 40 | entertainment |
I initially wrote this query for obtaining the above result:
SELECT SUM(amount) as totalAmount, tc.tag as category
FROM schema.tag_category tc
JOIN schema.ledger l
ON tc.category = ANY(l.tags)
WHERE l.user_id = '112233' AND DATE_PART('year', l.init_date)=2023
GROUP BY tc.tag
And tried out creating multiple indexes hoping that one would be used by the query:
create index on schema.tag_category USING btree (tag)
create index on schema.ledger using GIN (tags)
create index on schema.ledger using GIN (tags, user_id)
create index on schema.ledger using GIN (user_id, init_date, tags)
After running EXPLAIN ANALYZE I see that none of the indexes with the arrays is being used. After searching what the problem could be I found out that using array operators is required in order to work with array indexes. So I've updated my query:
SELECT SUM(amount) as totalAmount, tc.tag as category
FROM schema.tag_category tc
JOIN schema.ledger l
ON string_to_array(tc.category, ',') <@ l.tags
WHERE l.user_id = '112233'
AND DATE_PART('year', l.init_date)=2023
GROUP BY tc.tag
Still, looking at the Query Plan and none of the indexes with the array column is used when running the query. And to my surprise, the previous query without the array operator using the ANY operator is slightly faster. So I'm confused. Is there any way of speeding up the query by defining indexes or writing it differently?
