An index that covers both an ARRAY column and sorting by another column in postgres?
1
vote
1
answer
86
views
Let's say I have a table with an array column and an integer column:
CREATE TABLE records(
score INTEGER;
features INTEGER[];
);
Now I want to make queries that extract rows that have at least one of the requested features, and then I also want to order them by score.
So something like this:
SELECT *
FROM records
WHERE $1 && features
ORDER BY score;
But I have no idea what kind of index could be used to speed up this query. I have a BTREE on score and GIN on features, but I don't think the index on score is going to be used.
Another peculiarity is that *records* is actually a denormalised materialised view, and *features* are composed by inner joining with other tables (basically each feature is a row in *features* table and then there's another table that marks a record as having the specified feature). If the queries on the materialised view can't be reasonably sped up, then maybe I should get rid of it and just query a basic view.
Bonus question: what if there are also other INTEGER array columns which also need to be checked for containing a certain value? How are such queries usually handled?
Asked by DiplomateProgrammer
(11 rep)
Dec 14, 2024, 10:49 PM
Last activity: Dec 15, 2024, 12:18 AM
Last activity: Dec 15, 2024, 12:18 AM