0

I have a table like below.

Column |         Type             
-------------------------------
id     | integer                    
foo_1  | character varying(255)     
foo_2  | text                       
foo_3  | character varying(255)     
foo_4  | character varying(255)     
foo_5  | character varying(255)     
foo_6  | character varying(255)
foo_7  | character varying(255)     
foo_8  | character varying(255)     
foo_9  | timestamp without time zone
foo_10 | timestamp without time zone
foo_11 | timestamp without time zone
foo_12 | integer                    
foo_13 | boolean                    
foo_14 | integer                    
foo_15 | integer                    
foo_16 | integer                    
foo_17 | character varying          
foo_18 | character varying          
foo_19 | character varying          
foo_20 | character varying          
foo_21 | character varying          
foo_22 | boolean                    
foo_23 | character varying          
foo_24 | integer                    
foo_25 | integer                    
foo_26 | boolean                    

Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"index_items_on_foo_12_and_foo_25" UNIQUE, btree (foo_12, foo_25)
"index_items_on_foo_25" btree (foo_25)
"index_items_on_foo_12_and_foo_11" btree (foo_12, (foo_11 IS NULL), foo_11) WHERE foo_11 IS NULL
"index_items_on_foo_12_and_foo_18_and_foo_19" btree (foo_12, foo_18, foo_19)

Referenced by:
TABLE "bar_1" CONSTRAINT "fk_rails_0b050ce365" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_2" CONSTRAINT "fk_rails_3a20ccae66" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_3" CONSTRAINT "fk_rails_6a4a43dcc1" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_4" CONSTRAINT "fk_rails_92770ef494" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_5" CONSTRAINT "fk_rails_f9da65bc10" FOREIGN KEY (item_id) REFERENCES items(id)

I want to do an UPDATE:

UPDATE items
SET foo_1 = 'foo' || items.id || 'bar',
    foo_8 = NULL,
    foo_17 = NULL,
    foo_20 = NULL,
    foo_21 = NULL,
    foo_23 = NULL;

Does dropping any of the indexes of foreign keys might improve the time of the above update? None of the updated columns are in the indexes or constraints.

2
  • You may want to add the actual table and index definitions, as well as the actual query. As it is now your question is unanswerable. – mustaccio Sep 19 '18 at 12:44
  • @mustaccio I have updated my question. – pmichna Sep 19 '18 at 13:22
2

Since none of the updated columns are in indexes or constraints, you could conceivably do a 'HOT' (Heap-Only-Tuple) update, which doesn't need to maintain the indexes. But for that to work you have to have room in each table block to hold both the old and the new data at the same time. Once it needs to migrate rows to different blocks (because there is no room in the current block) then it needs to maintain all the indexes to point to the new block. Since you are updating every row in the table in a single transaction, this is unlikely to be practical as space for the old tuples cannot be recycled that quickly. You could help promote the HOT updates by setting the table fillfactor to a low value (<50) before loading the table data, but this unlikely to be an optimal solution. Better to just drop the index and constraints, or to delay creating them in the first place, as discussed in your related question.

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.