I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have tried to update in both cases (with and without indexed columns in update query), however there is not much change in query cost.

UPDATE mytable set COL1='test', IDXED_COL1=123, COL2='test', IDXED_COL2=2343
WHERE ID=1 //ID is primary key (using btree index)


UPDATE mytable set COL1='test', COL2='test'
WHERE ID=1 //ID is primary key (using btree index)

Is there no impact if the Table and the indexes are huge?

Edit 1

#EXPLAIN UPDATE MyTable set C2='Test', C3=1, IdxdC1 = 1, C4='Test', IdxdC2 = 2 WHERE ID = 1234 AND C1=1

 Update on "MyTable"  (cost=0.43..8.45 rows=1 width=798)
   -> Index Scan using "MyTable_ID_PK" on "MyTable"  (cost=0.43..8.45 rows=1 width=798)
         Index Cond: ("ID" = '1234'::numeric)
         Filter: ("C1" = '1'::numeric)

#EXPLAIN UPDATE MyTable set C2='Test', C3=1, C4='Test' WHERE ID = 1234 IdxdC1 = 1 AND IdxdC2 = 2 AND C1=1

 Update on "MyTable"  (cost=0.43..8.46 rows=1 width=773)
   ->  Index Scan using "MyTable_ID_PK" on "MyTable"  (cost=0.43..8.46 rows=1 width=773)
         Index Cond: ("ID" = '1234'::numeric)
         Filter: (("IdxdC1" = '1'::numeric) AND ("IdxdC2" = '2'::numeric) AND ("C1" = '1'::numeric))

(Both UPDATE's WHERE clause are does same in my situation)

  • Please share the query plans, without them it's impossible to tell. – dezso Apr 16 '19 at 9:06
  • @dezso I have added the EXPLAIN plan – Spike Apr 16 '19 at 9:34
  • 1
    Well, I thought you were talking about EXPLAIN ANALYZE, because you mentioned BEGIN; and ROLLBACK;. With simple EXPLAIN the query doesn't get executed, only a plan is generated. That aside, could you please share the EXPLAIN ANALYZE output instead? It will still not explicitly show the change on the index(es) you mention, but the timing should be at least slightly different. – dezso Apr 16 '19 at 10:14
  • @dezso, yeah you are right. I didn't think about it. I could see the execution time significantly improved while removing them from update statement and adding into WHERE clause. – Spike Apr 16 '19 at 10:41

Your Answer

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

Browse other questions tagged or ask your own question.