Re: Slow Inserts on 1 table? - Mailing list pgsql-general

What indexes are defined on both tables? Are there any triggers or
rules?

On Wed, Jul 20, 2005 at 09:50:54AM -0500, Dan Armbrust wrote:
> 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
>
> ---------------------------(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
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"