Skip to content
Advertisement

Postgresql update query taking too long to complete every time

I have PostgreSQL DB table user_book_details with 451007 records. The user_book_details table is getting populated on daily basis with around 1K new records.

I have the following query that is taking a long time(13 Hrs) to complete every time.

How I can rewrite the query to improve the performance? FYI, there is no index on user_id and book_id column.

Advertisement

Answer

Your query is okay:

For performance you want an index on user_book_details(user_id, book_id). I also think it would be faster written like this:

The first method uses the index to look up the values for each row (something that might be a little complicated when updating the same query). The second method aggregates the data and then joins in the values.

I should note that this value is easily calculated on the fly:

This might be preferable to trying to keep it up-to-date in the table.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement