PostgreSQL Multicolumn GIN Index with Ordering

Question

I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a given tag. For example, I want the top 20 most recently added records that have the element "blue" in the "tags" column.

What kind of index would be ideal to accomplish this? I tried using "btree_gin" with the "tags" and "date_inserted" columns together, but GIN does not seem to support DESC on the date_inserted column.

Answer 1

GIN indexes cannot be used for ordering, so don't bother including date_inserted in the index. You won't be able to support both the ORDER BY and the WHERE condition with an index in a single query; you will have to make up your mind or test which is better to support.

Answer 2

You can use a RUM index, provided by a 3rd party extension.

The index would look like this:

create index on thing using rum (tags rum_anyarray_addon_ops, date_inserted) with (attach='date_inserted', to='tags');

Since RUM ordering is by distance to a fixed point, you would need to pick a date in the future for the distance to order by. Assuming all inserted_dates must be in the past, you can use now() as that anchor:

select * from thing where tags @> '{blue}' order by date_inserted <=> now() limit 20;

RUM indexes have a high overhead. If 'blue' is a very rare tag, it could very well be faster to just use a GIN index on tags, and get the order by sorting all the qualifying rows. On the other hand if 'blue' is a very common tag, it could be faster to use an BTREE index on inserted_date and walk down that index until it finds 20 which pass the 'blue' filter. The RUM index is likely to be better when 'blue' is at an intermediate level of frequency (or if it were common early on, but is now rare)