Slow Inserts on 1 table?
От | Dan Armbrust |
---|---|
Тема | Slow Inserts on 1 table? |
Дата | |
Msg-id | 42DE64CE.4010301@gmail.com обсуждение исходный текст |
Ответы |
Re: Slow Inserts on 1 table?
Re: Slow Inserts on 1 table? |
Список | 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
В списке pgsql-general по дате отправления: