Slow Simple Update Query on Postgresql Database with 3 Million Rows

Slow simple update query on PostgreSQL database with 3 million rows

Take a look at this answer: PostgreSQL slow on a large table with arrays and lots of updates

First start with a better FILLFACTOR, do a VACUUM FULL to force table rewrite and check the HOT-updates after your UPDATE-query:

SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';

HOT updates are much faster when you have a lot of records to update. More information about HOT can be found in this article.

Ps. You need version 8.3 or better.

Update query too slow on big PostgreSQL table

Thank you @joop I resolved my problem. I had trigger to refresh materialized view. When I removed it, update query is taking just 0.123 ms instead of 727.487 ms, 6000 times faster.

I have organized materialized view different way.

Why did Postgres UPDATE take 39 hours?

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated.

The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a few minutes. Once the update completed, I re-created the indexes and was back in business. This probably won't help you at this point but it may someone else looking for answers.

I'd keep the indexes on the table you are pulling the data from. That one won't have to keep updating any indexes and should help with finding the data you want to update. It ran fine on a slow laptop.

PostgreSQL: speed up SELECT query in table with millions of rows

The index won't help.

Two solutions:

1. You chould either change the query to:

   WHERE dep_dt >= '2017-08-15 00:00:00' AND dep_dt < '2017-08-16 00:00:00'
   

   Then the index can be used.

2. Create an index on an expression:

   CREATE INDEX ON all_legs(((dep_dt AT TIME ZONE 'UTC')::date));
   

   (or a different time zone) and change the query to

   WHERE (dep_dt AT TIME ZONE 'UTC')::date = '2017-08-16'
   

   The AT TIME ZONE is necessary because otherwise the result of the cast would depend on your current TimeZone setting.

The first solution is simpler, but the second has the advantage that you can add price_ct to the index like this:

CREATE INDEX ON all_legs(((dep_dt AT TIME ZONE 'UTC')::date), price_ct);

Then you don't need a sort any more, and your query will be as fast as it can theoretically get.

---

Related Topics