Index slow down insertions... - Mailing list pgsql-novice

Hello,

Our postgres 9.0 DB has one table (the important one) where the bulk of
insertions is happening. We are looking more or less at around 15K to
20K insertions per minute and my measurements give me a rate of 0.60 to
1 msec per insertion. A summary of the table where the insertions are
happening is as follows:

-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_id
bigint NOT NULL DEFAULT
nextval('feed_all_y2012m07.messages_msg_id_seq'::regclass),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_type
smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  obj_id
integer NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
msg_date_rec timestamp without time zone NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_text
text NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
msg_expanded boolean NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  msg_time
time without time zone,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_accuracy boolean NOT NULL DEFAULT false,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_raim
boolean NOT NULL DEFAULT false,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_lon
integer NOT NULL DEFAULT (181 * 600000),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:  pos_lat
integer NOT NULL DEFAULT (91 * 60000),
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_georef1 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_georef2 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_georef3 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_georef4 character varying(2) NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
pos_point geometry,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_speed smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_course smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_heading smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_second smallint NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_radio integer NOT NULL,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_status ais_server.nav_status NOT NULL DEFAULT
'NOT_DEFINED'::ais_server.nav_status,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_turn smallint NOT NULL DEFAULT 128,
-- Inherited from table feed_all_y2012m07.ship_a_pos_messages:
ship_maneuver smallint NOT NULL,
   CONSTRAINT ship_a_pos_messages_wk0_pkey PRIMARY KEY (msg_id )

The table is created in table space "Data" while its indexes in table
space "Index" (a different HD). Now once the database is empty the
configuration is flying but of course this is not the case always. 5
days later and around 55,000,000 rows later the insertions are literally
so slow that the application server has to drop inserts in order to keep
up. To be precise we are looking now at 1 insertion every 5 to 10,
sometimes 25 msec!!

After lots of tuning both on the postgres server and the stored procs,
after installing 18G Ram and appropriately changing the shared_buffers,
working_mem etc, we realized that our index hard disk had 100%
utilization and essentially it was talking longer to update the indexes
than to update the table. Well I took a radical approach and dropped all
the indexes and... miracle, the db got back in to life, insertion went
back to a healthy 0.70msec but of course now I have no indexes. It is my
belief that I am doing something fundamentally wrong with the index
creation as 4 indexes cannot really bring a database to a halt. Here are
the indexes I was using:

CREATE INDEX idx_ship_a_pos_messages_wk0_date_pos
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (msg_date_rec , pos_georef1 , pos_georef2 , pos_georef3 , pos_georef4 )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_date_rec
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (msg_date_rec )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_object
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (obj_id , msg_type , msg_text , msg_date_rec )
TABLESPACE index;

CREATE INDEX idx_ship_a_pos_messages_wk0_pos
   ON feed_all_y2012m07.ship_a_pos_messages_wk0
   USING btree
   (pos_georef1 , pos_georef2 , pos_georef3 , pos_georef4 )
TABLESPACE index;

As I have run out of ideas any help will be really appreciated. For the
time being i can live without indexes but sooner or later people will
need to access the live data. I don't even dare to think what will
happen to the database if I only introduce a spatial GIS index that I
need. Question: Is there any possibility that I must include the primary
key into my index to "help" during indexing? If I remember well MS-SQL
has such a "feature".

Kind Regards
Yiannis