2

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:

  1. Reads in data from file.
  2. Performs certain modifications and creates an in-memory file.
  3. Creates a temporary table to hold the processed file data.
  4. COPYs the modified data into the temporary table.
  5. 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:

Rows: ~630k
Columns: 54
Indexes: 19
Triggers: 14
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?

  • 2
    19 indexes and 14 triggers can slow you down a lot. Can you combine the functionality of the triggers using less triggers to do the same? Can you get rid of some indexes? (Are you sure you use them all? Check Unused indexes). – joanolo Jul 3 '17 at 19:57
  • 1
    Can you paste the \d products and the contents of the 14 triggers. With the query UPDATE products SET detail = t.detail FROM tmp_products t WHERE t.product_id = products.product_id any update on detail will require an index rewrite on all affected indexes. Paste the result of EXPLAIN ANALYZE UPDATE... – Evan Carroll Jul 3 '17 at 20:00
  • @joanolo Modifying the triggers I can't do, but I may be able to remove some indexes. Thanks for the link. – That1Guy Jul 3 '17 at 20:04
  • 1
    @joanolo Yes, significant. By removing superfluous indexes, I was able to reduce the index count from 19 to 7 and the size from ~2.2GB to ~400MB. I'm still working on combining triggers, but I was able to trim some of those as well. In the end, my ~20 second query now returns in ~2 seconds, which for now is tolerable. – That1Guy Jul 18 '17 at 18:26
  • 1
    A 10x speed up is more than significant. Best of luck with the other optimisations. – joanolo Jul 18 '17 at 18:27
3

Things that can be done, although whether they help or not... is another story:

  1. Given that your UPDATE is quite simple, my first guess is your triggers are hurting your performance. Processing triggers is normally time-consuming, specially if they're written in any interpreted language (which means, more or less, they're not written in C). If you have possibility of checking with a development machine, test disabling all the triggers, and see what effect it has (time your queries!). Then reenable them one by one, and see which effect each one has on timings. You can find a few triggers hurting (a lot). If there are a few that consume a lot of time, have someone qualified revise them, optimize them, and even if necessary, rewrite them using C. My experience is that any kind of logging or auditing your inserts might make the process slower by (easily) a factor of 10. Take into account that, on top of your 14 triggers, the database might have added some more to make sure all constraints are met (CHECK, REFERENCES, UNIQUE, ...). It's not normally a good idea to try to disable those (and doing it is not straightforward, if possible at all).

  2. Try to find out whether you really need all the indexes in your setup. Check the explanations about Unused Indexes on PostgreSQL wiki. The way your query is working (only updating the column detail), if detail is not part of any index, this wouldn't have much of an influence. PostgreSQL should be able to perform a Heap Only Tuple (HOT) update, and the indexes wouldn't have any big effect.

  3. For HOT updates to succeed, you need some free space in your tables. So, make sure your tables have a fillfactor less than 100. From the docs on CREATE TABLE:

    fillfactor (integer)

    The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

    (emphasis mine)

  4. Consider having a covering index on your temporary table. That is:

    CREATE INDEX tmp_products_idx
    ON tmp_products
    USING BTREE
    (product_id, detail);
    
    ANALYZE tmp_products;
    

    This makes sense only if the length of detail is moderate. I don't think this will make much of a difference... because this might allow for an Index Only Scan for the source part of your update, but you won't be certain unless you try it.

Some more information about your execution plans will be necessary to provide finer advice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.