X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B2D09D1B53E for ; Wed, 29 Oct 2003 10:24:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89751-04 for ; Wed, 29 Oct 2003 06:24:06 -0400 (AST) Received: from mailserv.mobilecohesion.com (picard.mobilecohesion.com [80.4.157.11]) by svr1.postgresql.org (Postfix) with ESMTP id 0B44ED1B53A for ; Wed, 29 Oct 2003 06:23:44 -0400 (AST) Received: from pestilence.mobilecohesion.com ([10.12.10.31] helo=pestilence) by mailserv.mobilecohesion.com with esmtp (Exim 4.10) id 1AEnU2-00034r-00; Wed, 29 Oct 2003 10:23:22 +0000 Content-Type: text/plain; charset="iso-8859-15" From: Damien Dougan Organization: Mobile Cohesion To: pgsql-performance@postgresql.org Subject: Re: Very Poor Insert Performance Date: Wed, 29 Oct 2003 10:22:24 +0000 User-Agent: KMail/1.4.1 Cc: Tom Lane References: <200310271626.52839.damien.dougan@mobilecohesion.com> <2493.1067285528@sss.pgh.pa.us> In-Reply-To: <2493.1067285528@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310291022.24035.damien.dougan@mobilecohesion.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/720 X-Sequence-Number: 4468 On Monday 27 October 2003 8:12 pm, Tom Lane wrote: > Damien Dougan writes: > > Has anyone any ideas as to what could be causing the spiraling > > performance? > > You really haven't provided any information that would allow anything > but guesses, but I'll guess anyway: poor plans for foreign key checks? > See nearby threads. > > regards, tom lane Apologies for not including more info - I had been hoping that spiralling p= erformance was a known tell-tale sign of something :) Here is some additional information - sorry if its overload, but I figured = I should give an intro to the schema before showing the EXPLAIN results! Firstly, a quick schema overview for the relevant tables: contact has many contactparts address has many addressparts contact has many address Now, the above table relationships are connected via relationship tables (r= ather than foreign indexes directly to each other), so we have: contact rel_contact_has_contactpart address rel_address_has_addresspart (The reasons behind this are for meta-data purposes - our database is inten= ded to be very abstract from the code...) Table "public.contact" Column | Type | Modif= iers ------------------+-----------------------------+--------------------------= ------------------------- id | integer | default nextval('contact_= id_seq'::text) version | integer | default 1 contactid | character varying | enddate | timestamp without time zone | preferredaddress | character varying | startdate | timestamp without time zone | Indexes: "contact_id_idx" unique, btree (id) "contact_key" unique, btree (contactid) So we have an index on the meta-data related "id" and the externally visibl= e "contactid" values. The "id" is used with the rel_contact_has_XXX tables = (see below). Table "public.contactpart" Column | Type | Modifiers -------------+-------------------+-----------------------------------------= ------- id | integer | default nextval('contactpart_id_seq'::te= xt) version | integer | default 1 detailname | character varying | not null detailvalue | character varying | Indexes: "contactpart_id_idx" unique, btree (id) So we have an index on the meta-data related "id". =20=20=20=20 Table "public.address" Column | Type | Modifiers ---------+-----------------------------+-----------------------------------= --------- id | integer | default nextval('mc_address_id_seq= '::text) version | integer | default 1 enddate | timestamp without time zone | format | character varying | type | character varying | not null value | character varying | Indexes: "address_id_idx" unique, btree (id) "address_value_key" btree (value) So we have an index on the meta-data related "id". Table "public.addresspart" Column | Type | Modifiers -------------+-------------------+-----------------------------------------= ------- id | integer | default nextval('addresspart_id_seq'::te= xt) version | integer | default 1 detailname | character varying | not null detailvalue | character varying | Indexes: "addresspart_id_idx" unique, btree (id) So we have an index on the meta-data related "id". This is used with the re= l_address_has_addresspart table (see below). =20=20=20=20 Table "public.rel_contact_has_contactpart" Column | Type | Modifiers ----------------------+---------+----------- contact_id | integer | contactpart_id | integer | Indexes: "rel_contact_has_contactpart_idx2" unique, btree (contactpart_id) "rel_contact_has_contactpart_idx1" btree (contact_id) So we have a unique index on the contactpart and a non-unique index on the = contact (to reflect the 1:M relationship contact has contactparts) Table "public.rel_address_has_addresspart" Column | Type | Modifiers -------------------+---------+----------- address_id | integer | addresspart_id | integer | Indexes: "rel_address_has_addresspart_idx2" unique, btree (addresspart_id) "rel_address_has_addresspart_idx1" btree (address_id) So we have a unique index on the addresspart and a non-unique index on the = address (to reflect the 1:M relationship address has addressparts) =20=20=20=20 Table "public.rel_contact_has_address" Column | Type | Modifiers ----------------------+---------+----------- contact_id | integer | address_id | integer | Indexes: "rel_contact_has_address_idx2" unique, btree (address_id) "rel_contact_has_address_idx1" btree (contactdetails_id) So we have a unique index on the address and a non-unique index on the cont= act (to reflect the 1:M relationship contact has addresses) However, to add a layer of abstraction to the business logic, the underlyin= g tables are never directly exposed through anything other than public view= s. The public views combine the and into a single table. So we have 2 public views: PvContact which ties together the contact and co= ntactparts, and PvAddress which ties together the address and addresspart. View "public.pvcontact" Column | Type | Modifiers ------------------+-----------------------------+----------- version | integer | contactid | character varying | startdate | timestamp without time zone | enddate | timestamp without time zone | preferredaddress | character varying | firstname | character varying | lastname | character varying | (Note - firstname and lastname are dervied from the detailnames of contactp= art table) View "public.pvaddress" Column | Type | Modifiers -----------+-----------------------------+----------- version | integer | contactid | character varying | type | character varying | format | character varying | enddate | timestamp without time zone | npi | character varying | ton | character varying | number | character varying | prefix | character varying | addrvalue | character varying | link | character varying | house | character varying | street | character varying | town | character varying | city | character varying | county | character varying | postcode | character varying | state | character varying | zipcode | character varying | extension | character varying | (Note - number, prefix, link, house, street, town, city, postcode etc are = derived from the detailnames of addresspart table) For example, suppose we have 2 contactparts for a particular contact (with = unique id =3D y): FirstName and LastName, then the contactpart table would = have 2 rows like: id =3D x version =3D 1 partname =3D 'FirstName' partvalue =3D 'John' id =3D x+1 version =3D 1 partname =3D 'LastName' partvalue =3D 'Doe' Then the public view, PvContact, would look like: Version =3D 1 ContactId =3D y StartDate =3D ... EndDate =3D ... PreferredAddress =3D ... FirstName =3D John LastName =3D Doe All Create, Read, Update, Delete operations on the DB are performed by Stor= edProcedures (again, to help abstract the code from the DB). The SPs are ca= pable of dealing with externally (public view) advertised schemas, and ensu= ring data integrity across the underlying tables. Now, our problem seems to be the delays introduced by reading from the publ= ic views. I've taken some measurements of raw INSERTS that mirror what the = SPs are doing (but the data is invalid and its not correctly linked across = tables (which is what the StoredProcs are responsible for) - but the INSERT= S are happening in the same order and frequency for a valid data upload). W= e can upload 2000 sets of users+contacts+addresses in about 17 seconds. But= when it is done via the Stored Procedures (which do some inserts, some rea= ds, some more inserts etc to ensure tables are properly linked via the rela= tionships), this drops to 2 minutes for 2000. And the performance spirals d= own to less than 1 user+contact+address per second after a short while. First of all then, the definition of the PublicView PvAddress View definition: SELECT address.id AS addressid, address."version", contact.id AS contactid= , contact.contactid AS contactuuid, address."type", address.format, address= .enddate, address.value, rel_npi.npiid, rel_npi.npi, rel_ton.tonid, rel_ton= .ton, rel_number.numberid, rel_number.number, rel_prefix.prefixid, rel_pref= ix.prefix, rel_addrvalue.addrvalueid, rel_addrvalue.addrvalue, rel_link.lin= kid, rel_link.link, rel_house.houseid, rel_house.house, rel_street.streetid= , rel_street.street, rel_town.townid, rel_town.town, rel_city.cityid, rel_c= ity.city, rel_county.countyid, rel_county.county, rel_postcode.postcodeid, = rel_postcode.postcode, rel_state.stateid, rel_state.state, rel_zipcode.zipc= odeid, rel_zipcode.zipcode, rel_extension.extensionid, rel_extension.extens= ion FROM svcurrentcontactdetails contact, rel_contact_has_address rel_contac= t, svcurrentaddress address LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS np= iid, det.detailvalue AS npi FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Npi'::text) rel_npi ON address.id =3D rel_npi.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS to= nid, det.detailvalue AS ton FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Ton'::text) rel_ton ON address.id =3D rel_ton.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS nu= mberid, det.detailvalue AS number FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Number'::text) rel_number ON address.id =3D rel_number.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS pr= efixid, det.detailvalue AS prefix FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Prefix'::text) rel_prefix ON address.id =3D rel_prefix.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS ad= drvalueid, det.detailvalue AS addrvalue FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'AddrValue'::text) rel_addrvalue ON address.id =3D rel_addrvalue.addre= ssid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS li= nkid, det.detailvalue AS link FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Link'::text) rel_link ON address.id =3D rel_link.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS ho= useid, det.detailvalue AS house FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'House'::text) rel_house ON address.id =3D rel_house.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS st= reetid, det.detailvalue AS street FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Street'::text) rel_street ON address.id =3D rel_street.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS to= wnid, det.detailvalue AS town FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Town'::text) rel_town ON address.id =3D rel_town.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS ci= tyid, det.detailvalue AS city FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'City'::text) rel_city ON address.id =3D rel_city.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS co= untyid, det.detailvalue AS county FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'County'::text) rel_county ON address.id =3D rel_county.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS po= stcodeid, det.detailvalue AS postcode FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Postcode'::text) rel_postcode ON address.id =3D rel_postcode.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS st= ateid, det.detailvalue AS state FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'State'::text) rel_state ON address.id =3D rel_state.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS zi= pcodeid, det.detailvalue AS zipcode FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Zipcode'::text) rel_zipcode ON address.id =3D rel_zipcode.addressid LEFT JOIN ( SELECT rel.address_id AS addressid, rel.addresspart_id AS ex= tensionid, det.detailvalue AS extension FROM rel_address_has_addresspart rel, addresspart det WHERE rel.addresspart_id =3D det.id AND det.detailname::text= =3D 'Extension'::text) rel_extension ON address.id =3D rel_extension.addre= ssid WHERE contact.id =3D rel_contact.contact_id AND address.id =3D rel_contac= t.address_id; =20=20 (The JOINs are where our problems are below ...) hydradb=3D# explain select * from pvaddress where contactuuid =3D 'test' an= d type =3D 'sms' and format is null ; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------------- ----------------------------------------------- Merge Join (cost=3D42499.93..44975.38 rows=3D1 width=3D358) Merge Cond: ("outer".id =3D "inner".address_id) -> Merge Left Join (cost=3D42491.11..44957.05 rows=3D3795 width=3D323) Merge Cond: ("outer".id =3D "inner".address_id) -> Merge Left Join (cost=3D41822.20..44278.07 rows=3D3795 width= =3D305) Merge Cond: ("outer".id =3D "inner".address_id) -> Merge Left Join (cost=3D41153.29..43599.08 rows=3D3795 = width=3D287) Merge Cond: ("outer".id =3D "inner".address_id) -> Merge Left Join (cost=3D40484.39..42920.10 rows= =3D3795 width=3D269) Merge Cond: ("outer".id =3D "inner".address_id) -> Merge Left Join (cost=3D39815.48..42241.12 = rows=3D3795 width=3D251) Merge Cond: ("outer".id =3D "inner".addres= s_id) -> Merge Left Join (cost=3D39146.58..415= 62.13 rows=3D3795 width=3D233) Merge Cond: ("outer".id =3D "inner".= address_id) -> Merge Left Join (cost=3D38477.6= 7..40883.15 rows=3D3795 width=3D215) Merge Cond: ("outer".id =3D "i= nner".address_id) -> Merge Left Join (cost=3D3= 7808.76..40204.16 rows=3D3795 width=3D197) Merge Cond: ("outer".id = =3D "inner".address_id) -> Merge Left Join (co= st=3D37139.86..39525.18 rows=3D3795 width=3D179) Merge Cond: ("oute= r".id =3D "inner".address_id) -> Merge Left Joi= n (cost=3D36470.95..38846.20 rows=3D3795 width=3D161) Merge Cond: = ("outer".id =3D "inner".address_id) -> Merge Le= ft Join (cost=3D35802.04..38167.21 rows=3D3795 width=3D143) Merge = Cond: ("outer".id =3D "inner".address_id) -> Me= rge Left Join (cost=3D28634.40..30852.70 rows=3D3795 width=3D125) = Merge Cond: ("outer".id =3D "inner".address_id) = -> Merge Left Join (cost=3D21495.85..23569.10 rows=3D3795 width=3D107) = Merge Cond: ("outer".id =3D "inner".address_id) = -> Merge Left Join (cost=3D14328.21..16254.59 rows=3D3795 width=3D8= 9) = Merge Cond: ("outer".id =3D "inner".address_id) = -> Merge Left Join (cost=3D7102.23..8878.06 rows=3D3795 width= =3D71) = Merge Cond: ("outer".id =3D "inner".address_id) = -> Index Scan using address_id_idx on address (cost=3D0= .00..1633.07 rows=3D3795 width=3D53) = Filter: (((enddate IS NULL) OR (('now'::text)::time= stamp(6) with time zone < (enddate)::timestamp with time zone)) AND (("typ e")::text =3D 'sms'::text) AND (format IS NULL)) = -> Sort (cost=3D7102.23..7159.65 rows=3D22970 width=3D2= 2) = Sort Key: rel.address_id = -> Merge Join (cost=3D0.00..5438.34 rows=3D22970 = width=3D22) = Merge Cond: ("outer".id =3D "inner".addressli= ne_id) = -> Index Scan using addressline_id_idx on ad= dressline det (cost=3D0.00..2773.61 rows=3D22969 width=3D18) = Filter: ((detailname)::text =3D 'Npi'::= text) = -> Index Scan using rel_address_has_addressl= ine_idx2 on rel_address_has_addressline rel (cost=3D0.00..2082.13 rows=3D1= 181 93 width=3D8) = -> Sort (cost=3D7225.98..7286.76 rows=3D24310 width=3D22) = Sort Key: rel.address_id = -> Merge Join (cost=3D0.00..5455.09 rows=3D24310 width= =3D22) = Merge Cond: ("outer".id =3D "inner".addressline_id) = -> Index Scan using addressline_id_idx on addressl= ine det (cost=3D0.00..2773.61 rows=3D24309 width=3D18) = Filter: ((detailname)::text =3D 'Ton'::text) = -> Index Scan using rel_address_has_addressline_id= x2 on rel_address_has_addressline rel (cost=3D0.00..2082.13 rows=3D118193 = wid th=3D8) = -> Sort (cost=3D7167.64..7226.84 rows=3D23679 width=3D22) = Sort Key: rel.address_id = -> Merge Join (cost=3D0.00..5447.20 rows=3D23679 width=3D22) = Merge Cond: ("outer".id =3D "inner".addressline_id) = -> Index Scan using addressline_id_idx on addressline de= t (cost=3D0.00..2773.61 rows=3D23678 width=3D18) = Filter: ((detailname)::text =3D 'Number'::text) = -> Index Scan using rel_address_has_addressline_idx2 on = rel_address_has_addressline rel (cost=3D0.00..2082.13 rows=3D118193 width= =3D8) = -> Sort (cost=3D7138.56..7196.97 rows=3D23364 width=3D22) = Sort Key: rel.address_id = -> Merge Join (cost=3D0.00..5443.27 rows=3D23364 width=3D22) = Merge Cond: ("outer".id =3D "inner".addressline_id) = -> Index Scan using addressline_id_idx on addressline det (co= st=3D0.00..2773.61 rows=3D23363 width=3D18) = Filter: ((detailname)::text =3D 'Prefix'::text) = -> Index Scan using rel_address_has_addressline_idx2 on rel_ad= dress_has_addressline rel (cost=3D0.00..2082.13 rows=3D118193 width=3D8) -> So= rt (cost=3D7167.64..7226.84 rows=3D23679 width=3D22) = Sort Key: rel.address_id = -> Merge Join (cost=3D0.00..5447.20 rows=3D23679 width=3D22) = Merge Cond: ("outer".id =3D "inner".addressline_id) = -> Index Scan using addressline_id_idx on addressline det (cost=3D0= .00..2773.61 rows=3D23678 width=3D18) = Filter: ((detailname)::text =3D 'AddrValue'::text) = -> Index Scan using rel_address_has_addressline_idx2 on rel_address_= has_addressline rel (cost=3D0.00..2082.13 rows=3D118193 width=3D8) -> Sort (c= ost=3D668.91..669.16 rows=3D100 width=3D22) Sort K= ey: rel.address_id -> Ne= sted Loop (cost=3D0.00..665.58 rows=3D100 width=3D22) = -> Index Scan using addressline_detail_idx on addressline det (cost=3D0.0= 0..366.01 rows=3D99 width=3D18) = Index Cond: ((detailname)::text =3D 'Link'::text) = -> Index Scan using rel_address_has_addressline_idx2 on rel_address_has_ad= dressline rel (cost=3D0.00..3.01 rows=3D1 width=3D8) = Index Cond: (rel.addressline_id =3D "outer".id) -> Sort (cost=3D= 668.91..669.16 rows=3D100 width=3D22) Sort Key: re= l.address_id -> Nested L= oop (cost=3D0.00..665.58 rows=3D100 width=3D22) -> In= dex Scan using addressline_detail_idx on addressline det (cost=3D0.00..366= .01 rows=3D99 width=3D18) = Index Cond: ((detailname)::text =3D 'House'::text) -> In= dex Scan using rel_address_has_addressline_idx2 on rel_address_has_addressl= ine rel (cost=3D0.00..3.01 rows=3D1 width=3D8) = Index Cond: (rel.addressline_id =3D "outer".id) -> Sort (cost=3D668.91= ..669.16 rows=3D100 width=3D22) Sort Key: rel.addr= ess_id -> Nested Loop (= cost=3D0.00..665.58 rows=3D100 width=3D22) -> Index Sc= an using addressline_detail_idx on addressline det (cost=3D0.00..366.01 ro= ws=3D99 width=3D18) Index = Cond: ((detailname)::text =3D 'Street'::text) -> Index Sc= an using rel_address_has_addressline_idx2 on rel_address_has_addressline re= l (cost=3D0.00..3.01 rows=3D1 width=3D8) Index = Cond: (rel.addressline_id =3D "outer".id) -> Sort (cost=3D668.91..669.= 16 rows=3D100 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost= =3D0.00..665.58 rows=3D100 width=3D22) -> Index Scan usi= ng addressline_detail_idx on addressline det (cost=3D0.00..366.01 rows=3D9= 9 width=3D18) Index Cond: = ((detailname)::text =3D 'Town'::text) -> Index Scan usi= ng rel_address_has_addressline_idx2 on rel_address_has_addressline rel (co= st=3D0.00..3.01 rows=3D1 width=3D8) Index Cond: = (rel.addressline_id =3D "outer".id) -> Sort (cost=3D668.91..669.16 row= s=3D100 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00.= .665.58 rows=3D100 width=3D22) -> Index Scan using add= ressline_detail_idx on addressline det (cost=3D0.00..366.01 rows=3D99 widt= h=3D18) Index Cond: ((deta= ilname)::text =3D 'City'::text) -> Index Scan using rel= _address_has_addressline_idx2 on rel_address_has_addressline rel (cost=3D0= .00..3.01 rows=3D1 width=3D8) Index Cond: (rel.a= ddressline_id =3D "outer".id) -> Sort (cost=3D668.91..669.16 rows=3D10= 0 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00..665.5= 8 rows=3D100 width=3D22) -> Index Scan using addressli= ne_detail_idx on addressline det (cost=3D0.00..366.01 rows=3D99 width=3D18) Index Cond: ((detailname= )::text =3D 'County'::text) -> Index Scan using rel_addre= ss_has_addressline_idx2 on rel_address_has_addressline rel (cost=3D0.00..3= .01 rows=3D1 width=3D8) Index Cond: (rel.address= line_id =3D "outer".id) -> Sort (cost=3D668.91..669.16 rows=3D100 widt= h=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00..665.58 rows= =3D100 width=3D22) -> Index Scan using addressline_det= ail_idx on addressline det (cost=3D0.00..366.01 rows=3D99 width=3D18) Index Cond: ((detailname)::tex= t =3D 'Postcode'::text) -> Index Scan using rel_address_has= _addressline_idx2 on rel_address_has_addressline rel (cost=3D0.00..3.01 ro= ws=3D1 width=3D8) Index Cond: (rel.addressline_i= d =3D "outer".id) -> Sort (cost=3D668.91..669.16 rows=3D100 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00..665.58 rows=3D100= width=3D22) -> Index Scan using addressline_detail_id= x on addressline det (cost=3D0.00..366.01 rows=3D99 width=3D18) Index Cond: ((detailname)::text =3D = 'State'::text) -> Index Scan using rel_address_has_addre= ssline_idx2 on rel_address_has_addressline rel (cost=3D0.00..3.01 rows=3D1= width=3D8) Index Cond: (rel.addressline_id =3D = "outer".id) -> Sort (cost=3D668.91..669.16 rows=3D100 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00..665.58 rows=3D100 width= =3D22) -> Index Scan using addressline_detail_idx on a= ddressline det (cost=3D0.00..366.01 rows=3D99 width=3D18) Index Cond: ((detailname)::text =3D 'Zipco= de'::text) -> Index Scan using rel_address_has_addressline= _idx2 on rel_address_has_addressline rel (cost=3D0.00..3.01 rows=3D1 width= =3D8) Index Cond: (rel.addressline_id =3D "outer= ".id) -> Sort (cost=3D668.91..669.16 rows=3D100 width=3D22) Sort Key: rel.address_id -> Nested Loop (cost=3D0.00..665.58 rows=3D100 width=3D22) -> Index Scan using addressline_detail_idx on address= line det (cost=3D0.00..366.01 rows=3D99 width=3D18) Index Cond: ((detailname)::text =3D 'Extension':= :text) -> Index Scan using rel_address_has_addressline_idx2 = on rel_address_has_addressline rel (cost=3D0.00..3.01 rows=3D1 width=3D8) Index Cond: (rel.addressline_id =3D "outer".id) -> Sort (cost=3D8.83..8.83 rows=3D2 width=3D39) Sort Key: rel_contact.address_id -> Nested Loop (cost=3D0.00..8.82 rows=3D2 width=3D39) -> Index Scan using contact_key on contact (cost=3D0.00..5= .77 rows=3D1 width=3D35) Index Cond: ((contactid)::text =3D 'test'::text) Filter: (((startdate IS NULL) OR (('now'::text)::times= tamp(6) with time zone >=3D (startdate)::timestamp with time zone)) AND ((e= nddate IS NULL) OR (('now'::text)::timestamp(6) with time zone < (enddate):: timestamp with time zone))) -> Index Scan using rel_contact_has_address_idx1 on rel_con= tact_has_address rel_contact (cost=3D0.00..3.02 rows=3D2 width=3D8) Index Cond: ("outer".id =3D rel_contact.contact_id) (147 rows) As you can see, the PublicView is resulting in a huge nested loop, with an = index scan of the contact only occurring at the end. I would have expected = something more like: (1) An index scan of the contact table to determine the correct contact (2) An index scan of the address table using the rel_contact_has_address.ad= dress_id to obtain the (relatively small - max 16, and typically 2) address= es (3) A number of joins - at the same level rather than looping - to obtain t= he detailnames for the new column names of the public view As I said in my original email, these delays are after applying all the per= formance related enhancements (fsync off, increased backbuffers, sort memor= y etc) I have picked up from the archives and FAQ. The upload script was al= so modified to commit and vacuum analyze at different intervals without pro= viding any significant improvement. top reports the CPU usage at 99% - so I= believe its all looping of the above intermediate SELECTs that is causing = the spiralling delays as the number of rows increases. Again, any help would be very much appreciated! Damien