James Neff wrote:
Greetings,
Ive got a java application I am reading data from a flat file and
inserting it into a table. The first 2 million rows (each file
contained about 1 million lines) went pretty fast. Less than 40 mins to
insert into the database.
After that the insert speed is slow. I think I may be able to type the
data faster than what is being done by the java application on the third
file.
Table looks like this:
CREATE TABLE data_archive
(
id serial NOT NULL,
batchid integer NOT NULL,
claimid character varying(25) NOT NULL,
memberid character varying(45) NOT NULL,
raw_data text NOT NULL,
status integer DEFAULT 0,
line_number integer,
CONSTRAINT data_archive_pkey PRIMARY KEY (id)
)
there is also an index on batchid.
The insert command is like so:
"INSERT INTO data_archive (batchid, claimid, memberid, raw_data, status,
line_number) VALUES ('" + commandBatchID + "', '', '', '" + raw_data +
"', '1', '" + myFilter.claimLine + "');";
where the raw_data variable is the line from the file.
How can I find out what is causing this slow down and how do I speed it up?
Database is 8.2.0 on x86_64-unknown-linux-gnu.
There is nothing else running on this database server (other than
standard linux background programs). PS ax did not show anything else
running. No locks other than the occasional lock by the INSERT query.
I have done a FULL vacuum on this table but not reindex (running now).
Thanks in advance,
James
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Consider using copy
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
