0

I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.

My basic query is:

UPDATE us_demand
SET ...
FROM us_demand_addtl AS sub
WHERE us_demand.geoid10=sub.geoid10;

The tables each have a spatial column that is indexed (but not one of the updated columns), and geoid10 is NOT indexed in either table.

Machine Specs:

2x 7200RPM 1tb Drives in Raid

Intel i74790k - 4.00ghz quad core

32gb of RAM

Analyze Result: enter image description here

I have two questions. Why is this operation so slow, and what can I do to speed it up?

8
  • 1
    Give us the DDL of both tables - (CREATE TABLE blah....) and also, what is the spec of the machine that you're using? RAM, CPU, HDD config (RAID &c). It is difficult to even guess at performance issues without these basics. – Vérace Jul 16 '16 at 17:33
  • I've updated with the machine specs. The DDL on the tables are enormous, there are ~350 columns in the first table, and about 80 in the second table. Is there a particular point of interest that would help? – Grant H. Jul 16 '16 at 18:06
  • 1
    I'm attempting to merge about 70 of the columns from the 80 column table into the first table. I guess my primary question is whether or not I should have an index on the geoid10 columns of each table. – Grant H. Jul 16 '16 at 18:07
  • Please read wiki.postgresql.org/wiki/Slow_Query_Questions and show the result of EXPLAIN. – Daniel Vérité Jul 16 '16 at 18:12
  • 1
    @GrantH. Index geoid10 on at least the source table. – BillThor Jul 17 '16 at 13:51
1

I would index geoid10 on both tables. If you can commit partial results, commit every 1000 to 10,000 updates. You should be able to do the updates in a stored procedure. This may reduce the overhead for lock contention.

0

Were you IO bound? If so, then what is probably happening is that the Hash Join is producing rows to be updated in whatever order it happens to find them in (i.e. the order of the bit-masked hashes of the geoid10 values). It then sends the ctid (row pointer) and the new data to the UPDATE node. The UPDATE node is jumping all over the place updating rows in the order it is instructed to, leading to poor IO behavior.

If PostgreSQL were smarter, it would sort the output of the Hash Join into ctid order before passing them on to the UPDATE node. This would lead to more sequential IO in the UPDATE node.

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.