[PERFORM] PostgreSQL 9.0.1 on Windows performance tunning help please

[tuanhoanganh]

I have postgresql 9.0.1 on windows 2003 ent with 6GB ram, 4 disk SATA RAID
10.
I am running SymmetricDS to replication over WAN. But yesterday there was a
big problem, i updated alot of rows and query to gap data of SymmetricDS run
verry very slowly.

Here is my postgresql.conf to tunning PostgreSQL
effective_cache_size = 4GB
work_mem = 2097151
shared_buffers = 1GB

Here is query :
explain analyze select d.data_id, d.table_name, d.event_type, d.row_data,
d.pk_data, d.old_data, d.create_time, d.trigger_hist_id, d.channel_id,
d.transaction_id, d.source_node_id, d.external_data, '' from sym_data d
inner join sym_data_gap g on g.status='GP' and d.data_id between g.start_id
and g.end_id where d.channel_id='sale_transaction' order by d.data_id asc;

And here is result :
Nested Loop  (cost=0.00..1517515125.95 rows=26367212590 width=1403) (actual
time=14646.390..7745828.163 rows=2764140 loops=1)
  ->  Index Scan using sym_data_pkey on sym_data d  (cost=0.00..637148.72
rows=3129103 width=1403) (actual time=71.989..55643.665 rows=3124631
loops=1)
        Filter: ((channel_id)::text = 'sale_transaction'::text)
  ->  Index Scan using sym_data_gap_pkey on sym_data_gap g
 (cost=0.00..358.37 rows=8426 width=8) (actual time=2.459..2.459 rows=1
loops=3124631)
        Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id))
        Filter: (g.status = 'GP'::bpchar)
Total runtime: 7746577.478 ms

Here is table sym_data it have 437319 rows with data_id between start_id and
end_id of sym_data_gap has status = 'GP'

CREATE TABLE sym_data
(
  data_id serial NOT NULL,
  table_name character varying(50) NOT NULL,
  event_type character(1) NOT NULL,
  row_data text,
  pk_data text,
  old_data text,
  trigger_hist_id integer NOT NULL,
  channel_id character varying(20),
  transaction_id character varying(255),
  source_node_id character varying(50),
  external_data character varying(50),
  create_time timestamp without time zone,
  CONSTRAINT sym_data_pkey PRIMARY KEY (data_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sym_data OWNER TO postgres;

-- Index: idx_d_channel_id

-- DROP INDEX idx_d_channel_id;

CREATE INDEX idx_d_channel_id
  ON sym_data
  USING btree
  (data_id, channel_id);

And here is sym_data_gap table it have 57838 rows have status = 'GP'

CREATE TABLE sym_data_gap
(
  start_id integer NOT NULL,
  end_id integer NOT NULL,
  status character(2),
  create_time timestamp without time zone NOT NULL,
  last_update_hostname character varying(255),
  last_update_time timestamp without time zone NOT NULL,
  CONSTRAINT sym_data_gap_pkey PRIMARY KEY (start_id, end_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sym_data_gap OWNER TO postgres;

-- Index: idx_dg_status

-- DROP INDEX idx_dg_status;

CREATE INDEX idx_dg_status
  ON sym_data_gap
  USING btree
  (status);

Because the query run very slowly so data is not replication between to
distance. Please help me.

Sorry for my English
Tuan Hoang ANh