Slow Inserts on 1 table?

From: Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow Inserts on 1 table?
Date: 2005-07-20 14:50:54
Message-ID: 42DE64CE.4010301@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have one particular insert query that is running orders of magnitude
slower than other insert queries, and I cannot understand why.
For example, Inserts into "conceptProperty" (detailed below) are at
least 5 times faster than inserts into "conceptPropertyMultiAttributes".

When I am running the inserts, postmaster shows as pegging one CPU on
the Fedora Core 3 server it is running on at nearly 100%.

Any advice is appreciated. Here is a lot of info that may shed light on
the issue to someone with more experience than me:

Example Insert Query with data:
INSERT INTO conceptPropertyMultiAttributes (codingSchemeName,
conceptCode, propertyId, attributeName, attributeValue) VALUES ('NCI
MetaThesaurus', 'C0000005', 'T-2', 'Source', 'MSH2005_2004_10_12')

EXPLAIN ANALYZE output:
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.008
rows=1 loops=1)
Total runtime: 4.032 ms

Table Structure:
CREATE TABLE conceptpropertymultiattributes (
codingschemename character varying(70) NOT NULL,
conceptcode character varying(100) NOT NULL,
propertyid character varying(50) NOT NULL,
attributename character varying(50) NOT NULL,
attributevalue character varying(250) NOT NULL
);

Primary Key:
ALTER TABLE ONLY conceptpropertymultiattributes
ADD CONSTRAINT conceptpropertymultiattributes_pkey PRIMARY KEY
(codingschemename, conceptcode, propertyid, attributename, attributevalue);

Foreign Key:
ALTER TABLE ONLY conceptpropertymultiattributes
ADD CONSTRAINT f FOREIGN KEY (codingschemename, conceptcode,
propertyid) REFERENCES conceptproperty(codingschemename, conceptcode,
propertyid);

Structure of Table Referenced by Foreign Key:
CREATE TABLE conceptproperty (
codingschemename character varying(70) NOT NULL,
conceptcode character varying(100) NOT NULL,
propertyid character varying(50) NOT NULL,
property character varying(250) NOT NULL,
"language" character varying(32),
presentationformat character varying(50),
datatype character varying(50),
ispreferred boolean,
degreeoffidelity character varying(50),
matchifnocontext boolean,
representationalform character varying(50),
propertyvalue text NOT NULL
);

Primary Key:
ALTER TABLE ONLY conceptproperty
ADD CONSTRAINT conceptproperty_pkey PRIMARY KEY (codingschemename,
conceptcode, propertyid);

Thanks,

Dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2005-07-20 15:18:28 Re: Quotation marks in queries
Previous Message Rose, Juergen 2005-07-20 14:49:26 on delete rules on a view problem