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
