Re: 8.2.4 serious slowdown - Mailing list pgsql-general

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::doubleprecision 
                 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::timestampwithout 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::timestampwith 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
ANDleadcompcheck_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);