My Query to insert and retrieve takes time

From: Shivakumar Ramannavar <shivasr(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: My Query to insert and retrieve takes time
Date: 2011-10-31 11:21:51
Message-ID: CACdOLXa12h6oN406mqZAZaCJG3G6hK=eTx_YhthJZU6vomPkAA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Community,

Require some help, I am running postgres 9.1 database, which has table
having around 300,000 records, my problem is that it takes 8.314
milliseconds to update one record, and the below is the explain PLAN of
(terminal_id is UNIQUE index, and term_index is primary key)

EXPLAIN ANALYZE UPDATE terminal
SET unit_address=E'\\x00000015a3'
WHERE terminal_id = '320000'

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Update (cost=0.00..8.38 rows=1 width=64) (actual time=0.074..0.074 rows=0
loops=1)
-> Index Scan using terminal_ind2 on terminal (cost=0.00..8.38 rows=1
width=64) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: (terminal_id = 320000)
Total runtime: 0.182 ms

There are around 300,000 update operations and it is taking approx 80 min,
please let me know how to improve the performance.

Thanks!

--
Shivakumar Ramannavar

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shivakumar Ramannavar 2011-10-31 11:22:04 Shivakumar Ramannavar has invited you to open a Google mail account
Previous Message Achilleas Mantzios 2011-10-31 08:14:45 Re: SET search path