I've written a small program to import product-detail updates from file which takes much longer than expected. (I'll use trimmed-down examples for the sake of brevity.)
The program does the following:
- Reads in data from file.
- Performs certain modifications and creates an in-memory file.
- Creates a temporary table to hold the processed file data.
COPYs the modified data into the temporary table.
- Updates the actual table from the temporary table.
This all works fine, except the
UPDATE query takes ~20 seconds for a small file of ~2000 rows.
The temporary table looks like this:
CREATE TEMPORARY TABLE tmp_products ( product_id integer, detail text );
And my update query is really straightforward:
UPDATE products SET detail = t.detail FROM tmp_products t WHERE t.product_id = products.product_id
To speed things up, I tried the following with very little success:
Create a BTREE index on the temporary table.
CREATE INDEX tmp_products_idx ON tmp_products USING BTREE (product_id);
Creating a HASH index:
CREATE INDEX tmp_products_idx ON tmp_products USING HASH (product_id);
Neither index improved the update time significantly. Then I thought perhaps clustering the table would help, but that meant I couldn't use the HASH index. So I modified the queries in the program to use a BTREE index and then CLUSTER/ANALYZE:
CREATE INDEX tmp_products_idx ON tmp_products USING BTREE (product_id); -- Program inserts data CLUSTER tmp_products USING tmp_products_idx; ANALYZE tmp_products;
This didn't help anything either. I took one more stab at it by using both a BTREE and HASH index hoping that the CLUSTER would use the BTREE and the UPDATE would use the HASH:
CREATE INDEX tmp_products_btree_idx ON tmp_products USING BTREE (product_id); CREATE INDEX tmp_products_hash_idx ON tmp_products USING BTREE (product_id); -- Program inserts data CLUSTER tmp_products USING tmp_products_btree_idx; ANALYZE tmp_products;
And again, nothing helped. I'm still right where I left off - 20 seconds for 2000 rows. Normally 20 seconds for an update is tolerable in my workplace, but the 2000-row file is a small sample I'm using for testing. Larger files will take way too long.
A few details about the
products table if they matter:
Table Size: ~1.2GB
Index Size: ~2.2GB
I strongly suspect the bottle-neck is in one ore more of the triggers, however I'm not able to remove/modify those triggers. Is there anything I can do to improve the efficiency of my update?