0

We have an use case where we need to perform cascading deletions across multiple tables in our database. For this purpose, I have found that using GIN indexes as suggested in this answer is quite efficient, taking up 5-10 times less space than BTREE indexes for the same columns.

We're comfortable with explicitly type casting the values of conditions in our delete queries, such as

DELETE FROM table WHERE id IN (SELECT id FROM table WHERE user_id = 10::bigint)

The issue arises when considering our other SELECT queries. We're creating foreign key indexes on almost all the foreign keys for all the tables, and we want these indexes to be utilized efficiently. However, it's not feasible to apply type casting to the values in the WHERE conditions throughout the app. We are using Sequelize as our ORM.

Is there a way to configure Postgres such that the query planner uses the index efficiently, even when the values aren't type casted, in the same manner it does for BTREE indexes? Any guidance would be greatly appreciated.

Improve this question
3
  • Please explain in some detail why you think you need the type cast. Perhaps the operator family you are using is missing some operators.
    – Laurenz Albe
    Jul 20 at 6:39
  • @LaurenzAlbe I'm using the btree_gin extension to create GIN indexes for bigint types. But, my index isn't used unless I type cast the values in my query, similar to what's mentioned in this thread. Changing all my application queries isn't practical. You asked why I think I need to change the type. It seems postgresql won't use the index without it. You also mentioned missing operators in my operator family. Could this be the problem? If yes, how can I fix this?
    – Sri S
    Jul 23 at 15:34
  • I just created a table with a bigint column, created a GIN index on the column and was able to use it for comparisons with an integer without any need for a type cast. Please edit your question and add a sample table and index definition that exhibit the problem.
    – Laurenz Albe
    Jul 24 at 2:13

0

Reset to default

Your Answer

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