Sample Header Ad - 728x90

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