8.2.4 serious slowdown

Here are all of the data structures involved in this view.
Query Ran: select * from assemblycanbuild

CREATE OR REPLACE VIEW assemblycanbuild AS
SELECT assembliesbatchid,
CASE
WHEN min(
CASE
WHEN (stock::double precision - prioruse - quantity::double precision) >= 0::double precision THEN 100000000::double precision
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END) = 100000000::double precision THEN 'All'::character varying
ELSE min(
CASE
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END)::character varying
END AS canbuild
FROM assembliesstockbatchpriorexpected
WHERE quantity <> 0
GROUP BY assembliesbatchid;

CREATE OR REPLACE VIEW assembliesstockbatchpriorexpected AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, a.prioruse, COALESCE(sum(
CASE
WHEN COALESCE(e.promisedby::timestamp without time zone::timestamp with time zone, e.requestedby::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks IS NULL THEN f.issuedate
ELSE NULL::date
END::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks <> -1 THEN (f.issuedate + e.deliverywks * 7)::timestamp without time zone::timestamp with time zone
ELSE a.duedate + '1 day'::interval
END) <= a.duedate THEN COALESCE(e.quantity, 0) - COALESCE(e.deliveredsum, 0)
ELSE NULL::integer
END), 0::bigint) AS expectedbefore, a.qtyperunit
FROM assembliesstockbatchprioruse a
LEFT JOIN (pos f
JOIN poparts e ON f.poid = e.poid AND f.postatusid >= 20 AND f.postatusid <= 59 AND f.isrfq = false
JOIN manufacturerpartpn g ON g.pnid = e.pnid) ON e.partid = a.partid
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units, a.qtyperunit;

CREATE OR REPLACE VIEW assembliesstockbatchprioruse AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, COALESCE(sum(- b.quantity)::double precision, 0::double precision) AS prioruse, a.qtyperunit, a.leadfree
FROM assembliesstockbatch a
LEFT JOIN (allocatedassemblies b
JOIN assembliesbatch c ON b.assembliesbatchid = c.assembliesbatchid AND (c.assembliesbatchstatusid = 1 OR c.assembliesbatchstatusid = 2 OR c.assembliesbatchstatusid = 4 OR c.assembliesbatchstatusid = 7)
JOIN assemblies q ON q.assemblyid = c.assemblyid) ON a.partid = b.partid AND COALESCE(a.ownerid, 1) = 1 AND a.leadfree = q.leadfree AND (a.duedate > c.duedate OR a.duedate = c.duedate AND a.assembliesbatchid > c.assembliesbatchid)
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree;

CREATE OR REPLACE VIEW assembliesstockbatch AS
SELECT d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, d.units, - e.quantity AS quantity, COALESCE(c.stock, 0::bigint) AS stock, max(b.quantity) AS qtyperunit, a.leadfree
FROM assemblies a
JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid = b.partid
LEFT JOIN stockperowner_lead_ab() c(partid, ownerid, stock, leadstateid) ON c.partid = e.partid AND c.ownerid = 1 AND leadcompcheck_ab(a.leadfree, c.leadstateid)
LEFT JOIN stocklog f ON f.refid = d.batchid AND f.transtypeid = 3 AND f.partid = e.partid
WHERE (d.assembliesbatchstatusid = ANY (ARRAY[1, 2, 4, 7])) AND COALESCE(f.commited, false) = false
GROUP BY d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity, a.leadfree;

CREATE OR REPLACE FUNCTION stockperowner_lead_ab()
RETURNS SETOF stockperowner AS
$BODY$
declare
row stockperowner;
begin
for row in select partid,ownerid,sum(stock),2 from stockperowner
where leadstateid in (2,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
for row in select partid,ownerid,sum(stock),1 from stockperowner
where leadstateid in (1,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE VIEW stockperowner AS
SELECT a.partid, a.ownerid, sum(a.stock) AS stock, b.leadstateid
FROM stock a
JOIN manufacturerpartpn b ON a.pnid = b.pnid
WHERE b.compatibilitygradeid <= 400
GROUP BY a.partid, a.ownerid, b.leadstateid;

CREATE OR REPLACE FUNCTION leadcompcheck_ab(assmstat boolean, leadstateid integer)
RETURNS boolean AS
$BODY$
begin
if assmstat and leadstateid in (1,3,4) then
return true;
elsif not assmstat and leadstateid in (2,3,4) then
return true;
else
return false;
end if;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TABLE pos
(
poid integer NOT NULL DEFAULT nextval(('public.pos_poid_seq'::text)::regclass),
supplierid integer,
poref citext NOT NULL,
postatusid integer,
isrfq boolean,
posupplierref citext,
issuedate date,
confirmationdate date,
confirmationref citext,
promiseddeliverydate date,
deliverydate date,
comments text,
userid integer,
currencyid integer DEFAULT 1,
exchange double precision,
printedcomment text,
ownerid integer,
suppliercontactid integer,
readydate date,
courierid integer,
couriercontact citext,
courierdate date,
shipmentdoc citext,
suppliercourier boolean,
suppliercourierdetails citext,
fob boolean,
fobmfgname integer,
attachments text,
paymentorder integer,
paymentdelivery integer,
paymentcredit integer,
creditdays integer,
currentplus boolean,
problems text,
clonedfrompoid integer,
followupcontactid integer,
lastmodifieddate timestamp without time zone,
filegenerated boolean NOT NULL DEFAULT false,
revision integer DEFAULT 0,
CONSTRAINT pos_pkey PRIMARY KEY (poid),
CONSTRAINT pos_courierid_fkey FOREIGN KEY (courierid)
REFERENCES couriers (courierid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_currencyid_fkey FOREIGN KEY (currencyid)
REFERENCES currencies (currencyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_followupcontactid_fkey FOREIGN KEY (followupcontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT pos_postatusid_fkey FOREIGN KEY (postatusid)
REFERENCES postatus (postatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_suppliercontactid_fkey FOREIGN KEY (suppliercontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pos_supplierid_fkey FOREIGN KEY (supplierid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
ALTER TABLE pos OWNER TO postgres;

-- Index: courieridpo

-- DROP INDEX courieridpo;

CREATE INDEX courieridpo
ON pos
USING btree
(courierid);

-- Index: ix_b010e4db_b3da_4618_8328_f47d77c917a9_

-- DROP INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_;

CREATE INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_
ON pos
USING btree
(currencyid);

-- Index: ix_isrfqpo

-- DROP INDEX ix_isrfqpo;

CREATE INDEX ix_isrfqpo
ON pos
USING btree
(isrfq);

-- Index: ix_pospoid

-- DROP INDEX ix_pospoid;

CREATE UNIQUE INDEX ix_pospoid
ON pos
USING btree
(poid);

-- Index: owneridpo

-- DROP INDEX owneridpo;

CREATE INDEX owneridpo
ON pos
USING btree
(ownerid);

-- Index: postatusidpo

-- DROP INDEX postatusidpo;

CREATE INDEX postatusidpo
ON pos
USING btree
(postatusid);

-- Index: supplieridpo

-- DROP INDEX supplieridpo;

CREATE INDEX supplieridpo
ON pos
USING btree
(supplierid);

-- Index: useridpo

-- DROP INDEX useridpo;

CREATE INDEX useridpo
ON pos
USING btree
(userid);

CREATE TABLE poparts
(
popartid integer NOT NULL DEFAULT nextval(('public.poparts_popartid_seq'::text)::regclass),
poid integer,
partid integer,
pnid integer,
quantity integer,
supplierquantity integer,
unitprice double precision,
requestedby date,
promisedby date,
deliveredby date,
deliverywks integer,
comments citext,
currencyid integer,
statusrequest boolean,
nobid boolean,
invoiceno citext,
paymentsatus integer,
purchaseagreemet boolean,
deliveredsum integer DEFAULT 0,
fkpoitemstatusid integer,
bestprice double precision,
bestpricecomments citext,
linenumber integer,
intotal boolean NOT NULL DEFAULT true,
mpqqty integer,
lastmodifieddate timestamp without time zone,
CONSTRAINT poparts_pkey PRIMARY KEY (popartid),
CONSTRAINT poparts_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_poid_fkey FOREIGN KEY (poid)
REFERENCES pos (poid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE poparts OWNER TO postgres;

-- Index: currencyidpp

-- DROP INDEX currencyidpp;

CREATE INDEX currencyidpp
ON poparts
USING btree
(currencyid);

-- Index: ix_manufacturerpartpnpoparts

-- DROP INDEX ix_manufacturerpartpnpoparts;

CREATE INDEX ix_manufacturerpartpnpoparts
ON poparts
USING btree
(pnid);

-- Index: ix_partspoparts

-- DROP INDEX ix_partspoparts;

CREATE INDEX ix_partspoparts
ON poparts
USING btree
(partid);

-- Index: ix_pospoparts

-- DROP INDEX ix_pospoparts;

CREATE INDEX ix_pospoparts
ON poparts
USING btree
(poid);

-- Index: popartid

-- DROP INDEX popartid;

CREATE INDEX popartid
ON poparts
USING btree
(popartid);

CREATE TABLE manufacturerpartpn
(
pnid integer NOT NULL DEFAULT nextval(('public.manufacturerpartpn_pnid_seq'::text)::regclass),
partid integer,
manufacturerid integer,
manufacturerpn citext,
manufacturerdatasheet text,
mpq integer,
unitid integer,
comments citext,
compatibilitygradeid integer,
pnstatusid integer,
lifecycleid integer DEFAULT 100,
translatempq boolean NOT NULL DEFAULT false,
leadstateid integer,
parentid integer,
CONSTRAINT manufacturerpartpn_pkey PRIMARY KEY (pnid),
CONSTRAINT manufacturerpartpn_compatibilitygradeid_fkey FOREIGN KEY (compatibilitygradeid)
REFERENCES partcompatibility (compatibilitygradeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_lifecycleid_fkey FOREIGN KEY (lifecycleid)
REFERENCES partlifecycle (lifecycleid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_manufacturerid_fkey FOREIGN KEY (manufacturerid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT manufacturerpartpn_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE manufacturerpartpn OWNER TO postgres;

-- Index: ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_

-- DROP INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_;

CREATE INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_
ON manufacturerpartpn
USING btree
(compatibilitygradeid);

-- Index: ix_manufacturerpartpnpnid

-- DROP INDEX ix_manufacturerpartpnpnid;

CREATE UNIQUE INDEX ix_manufacturerpartpnpnid
ON manufacturerpartpn
USING btree
(pnid);

-- Index: ix_manufacturersmanufacturerpartpn

-- DROP INDEX ix_manufacturersmanufacturerpartpn;

CREATE INDEX ix_manufacturersmanufacturerpartpn
ON manufacturerpartpn
USING btree
(manufacturerid);

-- Index: ix_partlifecyclemanufacturerpartpn

-- DROP INDEX ix_partlifecyclemanufacturerpartpn;

CREATE INDEX ix_partlifecyclemanufacturerpartpn
ON manufacturerpartpn
USING btree
(lifecycleid);

-- Index: ix_partsmanufacturerpartpn

-- DROP INDEX ix_partsmanufacturerpartpn;

CREATE INDEX ix_partsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(partid);

-- Index: ix_unitsmanufacturerpartpn

-- DROP INDEX ix_unitsmanufacturerpartpn;

CREATE INDEX ix_unitsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(unitid);

-- Index: mpplsi

-- DROP INDEX mpplsi;

CREATE INDEX mpplsi
ON manufacturerpartpn
USING btree
(leadstateid);

CREATE TABLE allocatedassemblies
(
allocatedassembliesid integer NOT NULL DEFAULT nextval(('public.allocatedassemblies_allocatedassembliesid_seq'::text)::regclass),
assembliesbatchid integer,
partid integer,
ownerid integer,
quantity integer,
commitdate timestamp without time zone,
userid integer,
comments citext,
CONSTRAINT pk_allocatedassemblies PRIMARY KEY (allocatedassembliesid),
CONSTRAINT fk_allocatedassemblies_assembliesbatchid FOREIGN KEY (assembliesbatchid)
REFERENCES assembliesbatch (assembliesbatchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_allocatedassemblies_partid FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE allocatedassemblies OWNER TO postgres;

-- Index: fki_allocatedassemblies_assembliesbatchid

-- DROP INDEX fki_allocatedassemblies_assembliesbatchid;

CREATE INDEX fki_allocatedassemblies_assembliesbatchid
ON allocatedassemblies
USING btree
(assembliesbatchid);

-- Index: fki_allocatedassemblies_partid

-- DROP INDEX fki_allocatedassemblies_partid;

CREATE INDEX fki_allocatedassemblies_partid
ON allocatedassemblies
USING btree
(partid);

CREATE TABLE assembliesbatch
(
assembliesbatchid integer NOT NULL DEFAULT nextval(('public.assembliesbatch_assembliesbatchid_seq'::text)::regclass),
batchid integer,
assemblyid integer,
units integer,
comments citext,
lastmodified timestamp without time zone,
ab_options citext,
buildprice double precision,
duedate timestamp with time zone DEFAULT (('now'::text)::date + '49 days'::interval),
customerid integer,
allocatedunits integer,
canbuild citext,
entrydate timestamp without time zone DEFAULT ('now'::text)::date,
assembliesbatchstatusid integer DEFAULT 1,
customername citext,
currentsort integer,
bomprice double precision,
originalunits integer,
quotationitemid integer,
CONSTRAINT assembliesbatch_pkey PRIMARY KEY (assembliesbatchid),
CONSTRAINT assembliesbatch_assembliesbatchstatus_id FOREIGN KEY (assembliesbatchstatusid)
REFERENCES assembliesbatchstatus (assembliesbatchstatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_quotationitemid_fkey FOREIGN KEY (quotationitemid)
REFERENCES sales.quotationitems (quotationitemid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE assembliesbatch OWNER TO postgres;

-- Index: fki_assembliesbatch_assembliesbatchstatus_id

-- DROP INDEX fki_assembliesbatch_assembliesbatchstatus_id;

CREATE INDEX fki_assembliesbatch_assembliesbatchstatus_id
ON assembliesbatch
USING btree
(assembliesbatchstatusid);

-- Index: ix_080c8ff0_5017_42a2_a174_28095b85106e_

-- DROP INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_;

CREATE INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_
ON assembliesbatch
USING btree
(assemblyid);

CREATE TABLE assemblies
(
assemblyid integer NOT NULL DEFAULT nextval(('public.assemblies_assemblyid_seq'::text)::regclass),
assemblyname citext NOT NULL,
assemblytypeid integer DEFAULT 100,
productid integer,
leadfree boolean NOT NULL DEFAULT true,
CONSTRAINT assemblies_pkey PRIMARY KEY (assemblyid),
CONSTRAINT assemblies_assemblytypeid_fkey FOREIGN KEY (assemblytypeid)
REFERENCES assemblytype (assemblytypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assemblies_productid_fkey FOREIGN KEY (productid)
REFERENCES products (productid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT uix_assemblies_assemblyname UNIQUE (assemblyname)
)
WITH OIDS;
ALTER TABLE assemblies OWNER TO postgres;

-- Index: ix_assemblytypeassemblies

-- DROP INDEX ix_assemblytypeassemblies;

CREATE INDEX ix_assemblytypeassemblies
ON assemblies
USING btree
(assemblytypeid);

-- Index: ix_leadfree

-- DROP INDEX ix_leadfree;

CREATE INDEX ix_leadfree
ON assemblies
USING btree
(leadfree);

-- Index: ix_relationship58

-- DROP INDEX ix_relationship58;

CREATE INDEX ix_relationship58
ON assemblies
USING btree
(productid);

-- Index: uix_assemblies_assemblyname

-- DROP INDEX uix_assemblies_assemblyname;

CREATE UNIQUE INDEX uix_assemblies_assemblyname
ON assemblies
USING btree
(assemblyname);

CREATE TABLE partsassembly
(
partsassemblyid integer NOT NULL DEFAULT nextval(('public.partsassembly_partsassemblyid_seq'::text)::regclass),
partid integer NOT NULL,
assemblyid integer NOT NULL,
quantity double precision,
unitid integer,
CONSTRAINT partsassembly_pkey PRIMARY KEY (partsassemblyid),
CONSTRAINT partsassembly_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE partsassembly OWNER TO postgres;

-- Index: assemblyidpa

-- DROP INDEX assemblyidpa;

CREATE INDEX assemblyidpa
ON partsassembly
USING btree
(assemblyid);

-- Index: idx_u_assidpartid

-- DROP INDEX idx_u_assidpartid;

CREATE UNIQUE INDEX idx_u_assidpartid
ON partsassembly
USING btree
(partid, assemblyid);

-- Index: ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_

-- DROP INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_;

CREATE INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_
ON partsassembly
USING btree
(partid);

-- Index: ix_5b3dd218_7383_402a_90e2_12458dd570ea_

-- DROP INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_;

CREATE INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_
ON partsassembly
USING btree
(assemblyid);

-- Index: ix_unitspartsassembly

-- DROP INDEX ix_unitspartsassembly;

CREATE INDEX ix_unitspartsassembly
ON partsassembly
USING btree
(unitid);

-- Index: partidpa

-- DROP INDEX partidpa;

CREATE INDEX partidpa
ON partsassembly
USING btree
(partid);

-- Index: partsassemblyid

-- DROP INDEX partsassemblyid;

CREATE INDEX partsassemblyid
ON partsassembly
USING btree
(partsassemblyid);
CREATE TABLE stocklog
(
stocklogid integer NOT NULL DEFAULT nextval(('public.stocklog_stocklogid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
quantity integer,
transtypeid integer,
out_deleted boolean,
refid integer,
poid integer,
commited boolean,
commitdate timestamp without time zone,
userid integer,
comments citext,
stocklocationid integer,
scanned boolean NOT NULL DEFAULT false,
scanneddate timestamp without time zone,
CONSTRAINT stocklog_pkey PRIMARY KEY (stocklogid),
CONSTRAINT stocklog_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stocklog_transtypeid_fkey FOREIGN KEY (transtypeid)
REFERENCES transtypes (transtypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stocklog OWNER TO postgres;

-- Index: ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_

-- DROP INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_;

CREATE INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_
ON stocklog
USING btree
(ownerid);

-- Index: ix_manufacturerpartpnstocklog

-- DROP INDEX ix_manufacturerpartpnstocklog;

CREATE INDEX ix_manufacturerpartpnstocklog
ON stocklog
USING btree
(pnid);

-- Index: ix_partsstocklog

-- DROP INDEX ix_partsstocklog;

CREATE INDEX ix_partsstocklog
ON stocklog
USING btree
(partid);

-- Index: ix_transtypesstocklog

-- DROP INDEX ix_transtypesstocklog;

CREATE INDEX ix_transtypesstocklog
ON stocklog
USING btree
(transtypeid);

-- Index: owneridsl

-- DROP INDEX owneridsl;

CREATE INDEX owneridsl
ON stocklog
USING btree
(ownerid);

-- Index: partidsl

-- DROP INDEX partidsl;

CREATE INDEX partidsl
ON stocklog
USING btree
(partid);

-- Index: poidsl

-- DROP INDEX poidsl;

CREATE INDEX poidsl
ON stocklog
USING btree
(poid);

-- Index: referenceidsl

-- DROP INDEX referenceidsl;

CREATE INDEX referenceidsl
ON stocklog
USING btree
(refid);

-- Index: stocklogid

-- DROP INDEX stocklogid;

CREATE INDEX stocklogid
ON stocklog
USING btree
(stocklogid);

-- Index: targetidsl

-- DROP INDEX targetidsl;

CREATE INDEX targetidsl
ON stocklog
USING btree
(transtypeid);

-- Index: useridsl

-- DROP INDEX useridsl;

CREATE INDEX useridsl
ON stocklog
USING btree
(userid);

REATE TABLE stock
(
stockid integer NOT NULL DEFAULT nextval(('public.stock_stockid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
stock integer NOT NULL DEFAULT 0,
stocklocationid integer,
batchid integer,
CONSTRAINT stock_pkey PRIMARY KEY (stockid),
CONSTRAINT stock_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stock_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stock OWNER TO postgres;

-- Index: ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_

-- DROP INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_;

CREATE INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_
ON stock
USING btree
(ownerid);

-- Index: ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_

-- DROP INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_;

CREATE INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_
ON stock
USING btree
(partid);

-- Index: ix_manufacturerpartpnstock

-- DROP INDEX ix_manufacturerpartpnstock;

CREATE INDEX ix_manufacturerpartpnstock
ON stock
USING btree
(pnid);

-- Index: ownerids

-- DROP INDEX ownerids;

CREATE INDEX ownerids
ON stock
USING btree
(ownerid);

-- Index: partids

-- DROP INDEX partids;

CREATE INDEX partids
ON stock
USING btree
(partid);

2008/1/10, Sim Zacks <sim@compulab.co.il>:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

The data is exactly the same on the 2 servers.

To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
and the query took about 60 seconds.

On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
On 8.0.1, it didn't.

I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.

I have seen performance degradation at every new version since 7.3.
But now 8.3 is a complete disaster. It could be that my most expensive
query is just a corner case, but I don't believe it. I posted about it
but the whole thread disappeared from the archives. It can still be
found here:

http://archives.free.net.ph/message/20080105.004509.22be255d.es.html

Could you try 8.3 and see what happens? Keep the emails in case this
thread mysteriously disappears.

Regards, Clodoaldo Pinto Neto

On 1/10/08, Sim Zacks <sim@compulab.co.il> wrote:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.

Kind regards,
Isak

On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote:

I posted about it
but the whole thread disappeared from the archives. It can still be
found here:

http://archives.free.net.ph/message/20080105.004509.22be255d.es.html

Huh? It's right there:
http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy

2008/1/10, Martijn van Oosterhout <kleptog@svana.org>:

On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote:

I posted about it
but the whole thread disappeared from the archives. It can still be
found here:

http://archives.free.net.ph/message/20080105.004509.22be255d.es.html

Huh? It's right there:
http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Where did you get that url? I can't find it here:

http://archives.postgresql.org/pgsql-general/2008-01/threads.php

Regards, Clodoaldo Pinto Neto

On 10/01/2008 16:11, Clodoaldo wrote:

Where did you get that url? I can't find it here:

http://archives.postgresql.org/pgsql-general/2008-01/threads.php

It's on page 2 of the list.....click "Next", and then it's a little over
half-way down.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Clodoaldo escribi�:

2008/1/10, Martijn van Oosterhout <kleptog@svana.org>:

http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Where did you get that url? I can't find it here:

http://archives.postgresql.org/pgsql-general/2008-01/threads.php

"Next page"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Clodoaldo <clodoaldo.pinto.neto@gmail.com> writes:

2008/1/10, Martijn van Oosterhout <kleptog@svana.org>:

Huh? It's right there:
http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Where did you get that url? I can't find it here:
http://archives.postgresql.org/pgsql-general/2008-01/threads.php

Try about halfway down the "next page".

regards, tom lane

On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:

2008/1/10, Sim Zacks <sim@compulab.co.il>:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

The data is exactly the same on the 2 servers.

To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
and the query took about 60 seconds.

On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
On 8.0.1, it didn't.

I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.

I have seen performance degradation at every new version since 7.3.

Then your experience has been exactly the opposite of mine.

But now 8.3 is a complete disaster. It could be that my most expensive
query is just a corner case, but I don't believe it.

So, what's the other explanation, all queries in 8.3 are slower, and
everyone who says it's faster is just lieing?

Could you try 8.3 and see what happens? Keep the emails in case this
thread mysteriously disappears.

Please stop the histrionics. If your new query is slower, post the
information here to help the hackers figure out why its slower and
help you fix it. There's no grand conspiracy to hide you poorly
performing query. There may be a negative reaction to your behaviour
that's hampering it getting any priority to get fixed, but I can't say
I'd blame the hackers on that one.

I meant I did Vacuum Analyze.
In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.

Sim

Isak Hansen wrote:

Show quoted text

On 1/10/08, Sim Zacks <sim@compulab.co.il> wrote:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.

Kind regards,
Isak

---------------------------(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

Most of the queries that I have tested work on 8.2.4 at least as fast as on 8.0.1.
This one has really thrown me for a loop.

Sim

Show quoted text

Could you try 8.3 and see what happens? Keep the emails in case this
thread mysteriously disappears.

Please stop the histrionics. If your new query is slower, post the
information here to help the hackers figure out why its slower and
help you fix it. There's no grand conspiracy to hide you poorly
performing query. There may be a negative reaction to your behaviour
that's hampering it getting any priority to get fixed, but I can't say
I'd blame the hackers on that one.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

2008/1/10, Scott Marlowe <scott.marlowe@gmail.com>:

On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:

2008/1/10, Sim Zacks <sim@compulab.co.il>:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

The data is exactly the same on the 2 servers.

To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
and the query took about 60 seconds.

On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
On 8.0.1, it didn't.

I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.

I have seen performance degradation at every new version since 7.3.

Then your experience has been exactly the opposite of mine.

I suspect some developers here make a living from supporting
postgresql and have real world experience with it. I'm not sure who
they are as I don't read the list often. Are you one of them? If yes
can you tell from your clients experience that batch inserts of 800
thousands rows are faster now, especially with 8.3?

But now 8.3 is a complete disaster. It could be that my most expensive
query is just a corner case, but I don't believe it.

So, what's the other explanation, all queries in 8.3 are slower, and
everyone who says it's faster is just lieing?

Not all queries, but sure my batch insert queries are slower. I don't
know the explanation. I just don't think that batch insert queries are
irrelevant and could be treated as corner case.

Could you try 8.3 and see what happens? Keep the emails in case this
thread mysteriously disappears.

Please stop the histrionics.

Yes, that was a big mistake and I apologize for it.

If your new query is slower, post the
information here to help the hackers figure out why its slower and
help you fix it.

I already did it in the mentioned thread and I did that trying to help
and I don't expect any special treatment. If the developers think it
is not a priority so be it. I can just keep 8.2 until it gets
unsupported and/or I find the time and motivation to migrate to
another db server. As it is now 8.3 performance for my most important
query it totally unacceptable.

Regards, Clodoaldo Pinto Neto

On Jan 10, 2008 11:12 AM, Clodoaldo <clodoaldo.pinto.neto@gmail.com> wrote:

2008/1/10, Scott Marlowe <scott.marlowe@gmail.com>:

I have seen performance degradation at every new version since 7.3.

Then your experience has been exactly the opposite of mine.

I suspect some developers here make a living from supporting
postgresql and have real world experience with it. I'm not sure who
they are as I don't read the list often. Are you one of them? If yes
can you tell from your clients experience that batch inserts of 800
thousands rows are faster now, especially with 8.3?

I am a user. One who is VERY happy both with the performance of
PostgreSQL and the support I get by having a direct line of support to
the developers here on these news groups. The developers make money
by working for companies that provide support. Those companies make
money by selling support. They sell support because PostgreSQL is
performant. Making it slower will not, in the long run, make them
more money.

I haven't tested 8.3 yet, as I've been too busy migrating our internal
servers from 7.4 to 8.2, and I am very very very happy with the
increase in performance we are seeing in all operations, including
bulk imports.

If your new query is slower, post the
information here to help the hackers figure out why its slower and
help you fix it.

I already did it in the mentioned thread and I did that trying to help
and I don't expect any special treatment. If the developers think it
is not a priority so be it.

Well, generally performance corner cases are important. But during
the rush from late beta to release probably not as much as they would
have been before beta. They don't wanna go making large changes to
the source code to accomodate a single case if it could negatively
affect a lot of other cases.

OTOH, if your case is strong enough, then it's quite likely you could
get some work done to fix it.

I can just keep 8.2 until it gets
unsupported and/or I find the time and motivation to migrate to
another db server.

Yes, because other db servers never have these types of problems...

As it is now 8.3 performance for my most important
query it totally unacceptable.

Please look for my post addressed to you elsewhere about this issue.

I don't an answer to your question, but an obvious difference is that
the "slow" query contains many more loops. (this may already have been
noted, I didn't see it posted however).

(showing just the loops with more than one loop)

-> Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31
rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685)
-> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5
width=20) (actual time=0.012..3.162 rows=1694 loops=3685)
-> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1
loops=3685)
-> Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual
time=2.395..78.855 rows=3851 loops=3684)
-> Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062
width=12) (actual time=0.009..35.493 rows=19062 loops=3684)
-> Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual
time=0.074..71.265 rows=593 loops=3684)
-> Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual
time=0.057..68.467 rows=593 loops=3684)
-> Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32)
(actual time=0.008..34.510 rows=16860 loops=3684)

On 1/10/08, Sim Zacks <sim@compulab.co.il> wrote:

I meant I did Vacuum Analyze.
In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.

The stats didn't look too far off, no.

Perhaps a suboptimal plan is picked due to configuration issues, e.g.
memory constraints? Could you post your postgresql.conf as well?

Kind regards,
Isak

Show quoted text

Sim

Isak Hansen wrote:

On 1/10/08, Sim Zacks <sim@compulab.co.il> wrote:

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.

Kind regards,
Isak

---------------------------(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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

It does contain a lot more loops, but it is the exact same query, so I don't understand why it would use that kind of plan

sim

Bricklen Anderson wrote:

Show quoted text

I don't an answer to your question, but an obvious difference is that
the "slow" query contains many more loops. (this may already have been
noted, I didn't see it posted however).

(showing just the loops with more than one loop)

-> Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31
rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685)
-> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5
width=20) (actual time=0.012..3.162 rows=1694 loops=3685)
-> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1
loops=3685)
-> Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual
time=2.395..78.855 rows=3851 loops=3684)
-> Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062
width=12) (actual time=0.009..35.493 rows=19062 loops=3684)
-> Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual
time=0.074..71.265 rows=593 loops=3684)
-> Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual
time=0.057..68.467 rows=593 loops=3684)
-> Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32)
(actual time=0.008..34.510 rows=16860 loops=3684)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Perhaps a suboptimal plan is picked due to configuration issues, e.g.
memory constraints? Could you post your postgresql.conf as well?

Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query.
As I mentioned, I tried running both 8.0.11 and 8.2.4 on the same hardware, so that I would see if it was a difference in the hardware or the database.

Sim

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'. Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units: kB = kilobytes MB = megabytes GB = gigabytes
# Time units: ms = milliseconds s = seconds min = minutes h = hours d = days

#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------

# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'# use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf'# ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'# write an extra PID file
# (change requires restart)

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432# (change requires restart)
max_connections = 100# (change requires restart)
# Note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3# (change requires restart)
#unix_socket_directory = ''# (change requires restart)
#unix_socket_group = ''# (change requires restart)
#unix_socket_permissions = 0777# octal
# (change requires restart)
#bonjour_name = ''# defaults to the computer name
# (change requires restart)

# - Security & Authentication -

#authentication_timeout = 1min# 1s-600s
#ssl = off# (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos
#krb_server_keyfile = ''# (change requires restart)
#krb_srvname = 'postgres'# (change requires restart)
#krb_server_hostname = ''# empty string matches any keytab entry
# (change requires restart)
#krb_caseins_users = off# (change requires restart)

# - TCP Keepalives -
# see 'man 7 tcp' for details

#tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0# TCP_KEEPCNT;
# 0 selects the system default

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB# min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB# min 800kB
#max_prepared_transactions = 5# can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB# min 64kB
#maintenance_work_mem = 16MB# min 1MB
#max_stack_depth = 2MB# min 100kB

# - Free Space Map -

max_fsm_pages = 153600# min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000# min 100, ~70 bytes each
# (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000# min 25
# (change requires restart)
#shared_preload_libraries = ''# (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0# 0-1000 milliseconds
#vacuum_cost_page_hit = 1# 0-10000 credits
#vacuum_cost_page_miss = 10# 0-10000 credits
#vacuum_cost_page_dirty = 20# 0-10000 credits
#vacuum_cost_limit = 200# 0-10000 credits

# - Background writer -

#bgwriter_delay = 200ms# 10-10000ms between rounds
#bgwriter_lru_percent = 1.0# 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5# 0-1000 buffers max written/round

#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = on# turns forced synchronization on or off
#wal_sync_method = fsync# the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on# recover from partial page writes
#wal_buffers = 64kB# min 32kB
# (change requires restart)
#commit_delay = 0# range 0-100000, in microseconds
#commit_siblings = 5# range 1-1000

# - Checkpoints -

#checkpoint_segments = 3# in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min# range 30s-1h
#checkpoint_warning = 30s# 0 is off

# - Archiving -

#archive_command = ''# command to use to archive a logfile segment
#archive_timeout = 0# force a logfile segment switch after this
# many seconds; 0 is off

#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0# measured on an arbitrary scale
#random_page_cost = 4.0# same scale as above
#cpu_tuple_cost = 0.01# same scale as above
#cpu_index_tuple_cost = 0.005# same scale as above
#cpu_operator_cost = 0.0025# same scale as above
#effective_cache_size = 128MB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5# range 1-10
#geqo_pool_size = 0# selects default based on effort
#geqo_generations = 0# selects default based on effort
#geqo_selection_bias = 2.0# range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10# range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8# 1 disables collapsing of explicit
# JOINs

#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'# Valid values are combinations of
# stderr, syslog and eventlog,
# depending on platform.

# This is used when logging to stderr:
#redirect_stderr = off# Enable capturing of stderr into log
# files
# (change requires restart)

# These are only used if redirect_stderr is on:
#log_directory = 'pg_log'# Directory where log files are written
# Can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
# Can include strftime() escapes
#log_truncate_on_rotation = off # If on, any existing log file of the same
# name as the new log file will be
# truncated rather than appended to. But
# such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
#log_rotation_age = 1d# Automatic rotation of logfiles will
# happen after that time. 0 to
# disable.
#log_rotation_size = 10MB# Automatic rotation of logfiles will
# happen after that much log
# output. 0 to disable.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice# Values, in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

#log_min_messages = notice# Values, in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

#log_error_verbosity = default# terse, default, or verbose messages

#log_min_error_statement = error# Values in order of increasing severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# fatal
# panic (effectively off)

#log_min_duration_statement = -1# -1 is disabled, 0 logs all statements
# and their durations.

#silent_mode = off# DO NOT USE without syslog or
# redirect_stderr
# (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_line_prefix = ''# Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no milliseconds)
# %m = timestamp with milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start timestamp
# %x = transaction id
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_statement = 'none'# none, ddl, mod, all
#log_hostname = off

#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#stats_command_string = on
#update_process_title = on

#stats_start_collector = on# needed for block or row stats
# (change requires restart)
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off# (change requires restart)

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on# enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
#autovacuum_naptime = 1min# time between autovacuum runs
autovacuum_vacuum_threshold = 250# min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 125# min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.1# fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.05# fraction of rel size before
# analyze
#autovacuum_freeze_max_age = 200000000# maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = -1# default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled
#vacuum_freeze_min_age = 100000000

# - Locale and Formatting -

datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#timezone_abbreviations = 'Default' # select the set of available timezone
# abbreviations. Currently, there are
# Default
# Australia
# India
# However you can also create your own
# file in share/timezonesets/.
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
# encoding

# These settings are initialized by initdb -- they might be changed
lc_messages = 'C' # locale for system error message
# strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

# - Other Defaults -

#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''

#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.

#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

add_missing_from = on
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#standard_conforming_strings = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on

# - Other Platforms & Clients -

#transform_null_equals = off

#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

On Jan 10, 2008 10:50 AM, Sim Zacks <sim@compulab.co.il> wrote:

I meant I did Vacuum Analyze.
In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.

FYI, a restore does NOT restore the stats, nor does it automatically
update them. You have to run an analyze after a restore to get the
stats updated.

On Jan 10, 2008 12:33 PM, Sim Zacks <sim@compulab.co.il> wrote:

Perhaps a suboptimal plan is picked due to configuration issues, e.g.
memory constraints? Could you post your postgresql.conf as well?

Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query.
As I mentioned, I tried running both 8.0.11 and 8.2.4 on the same hardware, so that I would see if it was a difference in the hardware or the database.

# -----------------------------
# PostgreSQL configuration file
# -----------------------------

shared_buffers = 24MB# min 128kB or max_connections*16kB

That's really low. Try setting it to something a bit more aggressive,
say 100MB to 500MB. On a machine with 2 Gig ram, that's a pretty
reasonable range.

#work_mem = 1MB# min 64kB

Try setting this a little higher, say 16 to 32 Megs.