0

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.

2 Answers 2

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.

3
  • I could append the timestamp to each tag, like blue-2022-12-12, red-2022-12-12, etc., and then do only ORDER_BY. In that case, how can I create an array index that will help with ORDER_BY?
    – xqzkio
    Dec 22, 2022 at 10:31
  • An index on an array (= GIN index) won't help. Filtering on array elements and sorting don't go together. No way. Dec 22, 2022 at 10:32
  • Thanks for the clarification.
    – xqzkio
    Dec 22, 2022 at 10:33
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.