Slow Inserts on 1 table?

Поиск
Список
Период
Сортировка
От Dan Armbrust
Тема Slow Inserts on 1 table?
Дата
Msg-id 42DE64CE.4010301@gmail.com
обсуждение исходный текст
Ответы Re: Slow Inserts on 1 table?  ("Jim C. Nasby" <decibel@decibel.org>)
Re: Slow Inserts on 1 table?  (Dan Armbrust <daniel.armbrust.list@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: "Rose, Juergen"
Дата:
Сообщение: on delete rules on a view problem
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: Quotation marks in queries