Optimize Postgres query by indexing data

Question

My existing application is running on Heroku and I'm using Postgres as database.

Now my queries are getting slow because of the growing amount of data. Here is my query

SELECT *
FROM my_table
WHERE my_table.is_deleted = $1
  AND my_table.id NOT IN (SELECT my_table_user_actions.qurb_id AS my_table_user_actions_qurb_id
                          FROM my_table_user_actions
                          WHERE my_table_user_actions.user_id = $2
                            AND my_table_user_actions.is_hidden = $3)
  AND my_table.block_x BETWEEN $4 AND $5
  AND my_table.block_y BETWEEN $6 AND $7
  AND my_table.id NOT IN (SELECT sponsored_qurb_log.qurb_id AS sponsored_qurb_log_qurb_id
                          FROM sponsored_qurb_log
                          WHERE sponsored_qurb_log.qurb_id = my_table.id
                            AND sponsored_qurb_log.hash = $8
                            AND sponsored_qurb_log.user_id = $9)) AS anon_1

This query is taking almost 10 seconds to execute on the server.

Now I'm willing to apply an index on following columns

is_deleted is of type boolean
block_x is of type int
block_y is of type int

These are the three columns. Here is_deleted is always set to false because I always wanted to get all those records which are not deleted. block_x and block_y are the columns which have latitude and longitude.

Please let me know what will be the index for the query.

Here is what I'm thinking about

Multi column index :

CREATE INDEX my_table_xandy_block ON my_table(blovk_x, block_y);

And Partial index for is_deleted:

CREATE INDEX is_deleted_index ON my_table(is_deleted) WHERE is_deleted IS FALSE;

Kindly check my queries and let me know what should I do to optimize my query. Since I'm not willing to change my query as I'll to deploy newer version of code.

Laurenz Albe · Accepted Answer · 2018-12-03 12:12:09Z

1

In general, you have to examine the EXPLAIN (ANALYZE, BUFFERS) output for the query to answer such a question.

But in your case it is simple: You will have to convert the NOT IN clauses into WHERE NOT EXISTS.

An example:

WHERE a.x NOT IN (
   SELECT b.y FROM b
)

should become

WHERE NOT EXISTS (
   SELECT 1 FROM b
   WHERE a.x = b.y
)

That way PostgreSQL can use an “antijoin” to process the query, which will be faster for bigger tables.

To speed up the query further, look ad the execution plan and add indexes as appropriate.

If you really refuse to rewrite the query, the best you can dobare the following indexes:

CREATE INDEX ON my_table_user_actions (user_id, is_hidden);

CREATE INDEX ON sponsored_qurb_log (qurb_id, hash, user_id);

edited Dec 3, 2018 at 12:12

answered Dec 3, 2018 at 11:56

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimize Postgres query by indexing data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related