First of all I want to thank you for all responses! I was overwhelmed with
it :D
Below you find the schema I'm currently using and the output of explain. I
removed all comments so the mail will be small, the schema is still work in
progress. I especially I need to take a look at the indexes. Any hints will
be appreciated.
best reghards,
Ries van Twisk
<-----------
Here you find the output of the explain again:
I cannot yet read the output of explain si I'm not sure if the output looks
good or bad.
echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE
projectcode=5" | psql testdb > /tmp/explain.txt
NOTICE: QUERY PLAN:
Hash Join (cost=26.28..39.00 rows=23 width=200)
-> Hash Join (cost=24.85..37.17 rows=23 width=182)
-> Hash Join (cost=23.43..35.34 rows=23 width=164)
-> Seq Scan on libitems lit (cost=0.00..7.39 rows=339
width=27)
-> Hash (cost=23.37..23.37 rows=23 width=137)
-> Hash Join (cost=11.05..23.37 rows=23 width=137)
-> Hash Join (cost=9.75..21.67 rows=23
width=120)
-> Seq Scan on libitems lif
(cost=0.00..7.39 rows=339 width=27)
-> Hash (cost=9.69..9.69 rows=23 width=93)
-> Hash Join (cost=4.76..9.69
rows=23 width=93)
-> Hash Join (cost=3.46..7.99
rows=23 width=76)
-> Hash Join
(cost=2.42..6.32 rows=69 width=63)
-> Seq Scan on
cablelist cl (cost=0.00..2.69 rows=69 width=41)
-> Hash
(cost=2.06..2.06 rows=106 width=22)
-> Seq Scan
on cabletypes ct (cost=0.00..2.06 rows=106 width=22)
-> Hash (cost=1.04..1.04
rows=1 width=13)
-> Seq Scan on
projectcodes pc (cost=0.00..1.04 rows=1 width=13)
-> Hash (cost=1.24..1.24
rows=24 width=17)
-> Seq Scan on
libconnections lcf (cost=0.00..1.24 rows=24 width=17)
-> Hash (cost=1.24..1.24 rows=24 width=17)
-> Seq Scan on libconnections lct
(cost=0.00..1.24 rows=24 width=17)
-> Hash (cost=1.34..1.34 rows=34 width=18)
-> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34
width=18)
-> Hash (cost=1.34..1.34 rows=34 width=18)
-> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34
width=18)
<------------------
CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
SELECT (CASE
WHEN $1 < $2
THEN $1 || $2
ELSE $2 || $1
END) as t;
' LANGUAGE SQL WITH (iscachable);
CREATE FUNCTION plpgsql_call_handler ()
RETURNS OPAQUE
AS '/usr/lib/postgresql/plpgsql.so'
LANGUAGE 'C';
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE cabletypes (
id SERIAL,
cabletype VARCHAR(24) NOT NULL CHECK ( length(cabletype) > 1 ), -- Naam
van de kabel
coretype VARCHAR(16) NOT NULL CHECK ( length(coretype) > 1 ) -- Type
kabel/aantal aders
);
CREATE UNIQUE INDEX cabletypes_idx ON cabletypes (id);
CREATE FUNCTION f_check_cabletypes() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
SELECT INTO check * FROM cabletypes WHERE cabletype=NEW.cabletype AND
coretype=NEW.coretype LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0001] cabletype and coretype combination already
exsists in cabletypes!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_cabletypes
BEFORE INSERT OR UPDATE ON cabletypes FOR EACH ROW
EXECUTE PROCEDURE f_check_cabletypes();
CREATE TABLE marktypes (
id SERIAL,
name VARCHAR(24) NOT NULL UNIQUE, -- Naam van de markering
color INTEGER NOT NULL -- Eventuele kleur
);
CREATE UNIQUE INDEX marktypes_idx ON marktypes (id);
CREATE TABLE projectcodes (
id SERIAL,
projectcode VARCHAR(16) NOT NULL UNIQUE, -- Project code naam
projectname VARCHAR(64) NOT NULL, -- Project uitleg
deleted BOOLEAN DEFAULT 'false' NOT NULL
);
CREATE UNIQUE INDEX projectcodes_idx ON projectcodes (id);
CREATE TABLE libitems (
id SERIAL,
projectcodeid INTEGER DEFAULT 0 NOT NULL REFERENCES projectcodes(id) ON
DELETE CASCADE,
name VARCHAR(32) NOT NULL UNIQUE -- Naam van de
item bv boiler
);
CREATE UNIQUE INDEX libitems_idx ON libitems(id);
CREATE FUNCTION f_check_libitems() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
-- Update van de name mag wel
IF TG_OP = ''UPDATE'' THEN
IF NEW.projectcodeid = OLD.projectcodeid AND NEW.name = OLD.name THEN
RETURN NEW;
END IF;
END IF;
-- Controleer of the combinatie projectcode en ribnummer unique is
SELECT INTO check * FROM libitems WHERE projectcodeid=new.projectcodeid AND
name=new.name LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0005] projectcodide and name combination already
exsists in shiplocations!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_linitems
BEFORE INSERT OR UPDATE ON libitems FOR EACH ROW
EXECUTE PROCEDURE f_check_libitems();
CREATE TABLE libconnections (
id SERIAL,
libitemid INTEGER CONSTRAINT libitemid_con NOT NULL REFERENCES
libitems(id) ON DELETE CASCADE,
name VARCHAR(32), -- Naam van de aansluiting aan
een item
cableno VARCHAR(8)
);
CREATE UNIQUE INDEX libconnections_idx ON libconnections(id);
CREATE FUNCTION f_check_libconnections() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
-- Update van de name mag wel
IF TG_OP = ''UPDATE'' THEN
IF NEW.libitemid = OLD.libitemid AND NEW.name = OLD.name THEN
RETURN NEW;
END IF;
END IF;
SELECT INTO check * FROM libconnections WHERE libitemid=NEW.libitemid AND
name=NEW.name LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0002] name and item combination already exsists in
libconnections!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_libconnections
BEFORE INSERT OR UPDATE ON libconnections FOR EACH ROW
EXECUTE PROCEDURE f_check_libconnections();
---------------
CREATE TABLE shiplocations (
id SERIAL,
projectcodeid INTEGER NOT NULL REFERENCES projectcodes(id) ON DELETE
CASCADE,
rib SMALLINT DEFAULT 0 NOT NULL CHECK (rib>0), -- rib nummer
name VARCHAR(32) NOT NULL, -- Naam van de
locatie (bv voorschip,
middenschip, achterschip)
loc SMALLINT DEFAULT 0 NOT NULL CHECK (loc>=0 AND loc<5)
);
CREATE VIEW shiplocationst AS SELECT id, projectcodeid, rib, name,
CASE loc WHEN 0 THEN 'ries'
when 1 THEN 'ries1'
when 2 THEN 'ries2'
ELSE 'other'
END
FROM shiplocations;
CREATE UNIQUE INDEX shiplocations_idx ON shiplocations(id);
CREATE TABLE cablelist (
id SERIAL,
cableno VARCHAR(8),
projectcodeid INTEGER CONSTRAINT cablelist_projectcodes_con NOT NULL
REFERENCES projectcodes(id) ON DELETE CASCADE,
fromconnid INTEGER CONSTRAINT cablelist_fromconnid_con NOT NULL
REFERENCES libconnections(id) ON DELETE CASCADE,
toconnid INTEGER CONSTRAINT cablelist_toconnid_con NOT NULL REFERENCES
libconnections(id) ON DELETE CASCADE,
fromshiplocationid INTEGER CONSTRAINT cablelist_fromshiplocationid_con
NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
toshiplocationid INTEGER CONSTRAINT cablelist_toshiplocationid_con NOT
NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
marktypesid INTEGER CONSTRAINT cablelist_tomarktypeid_con NOT NULL
REFERENCES marktypes(id) ON DELETE CASCADE,
cabletypeid INTEGER CONSTRAINT cablelist_cabletypeid_con NOT NULL
REFERENCES cabletypes(id) ON DELETE CASCADE,
cut BOOLEAN DEFAULT 'false' NOT NULL,
placed BOOLEAN DEFAULT 'false' NOT NULL,
ok BOOLEAN DEFAULT 'false'
);
CREATE VIEW full_cablelist AS
SELECT cl.id,
cl.cableno AS cableno,
pc.projectcode AS projectcode,
pc.id AS projectcodeid,
lcf.name AS fconnection, lct.name AS tconnection,
lif.name AS fitem, lit.name AS titem,
slf.rib AS frib,slt.rib AS trib,
slf.name AS fname, slt.name AS tname,
ct.cabletype AS cabletype, ct.coretype AS coretype,
cl.cut,
cl.placed,
cl.ok
FROM cablelist AS cl,
libconnections AS lcf, libconnections AS lct,
libitems AS lif, libitems AS lit,
shiplocations AS slf, shiplocations AS slt,
projectcodes AS pc,
cabletypes AS ct
WHERE
pc.id=cl.projectcodeid AND
lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
ct.id=cl.cabletypeid
;
CREATE FUNCTION f_find_free_cableno(INTEGER,VARCHAR(4)) RETURNS VARCHAR(8)
AS '
DECLARE
pcid ALIAS FOR $1;
prefix ALIAS FOR $2;
cnmax INTEGER;
newcableno INTEGER;
CHECK RECORD;
cablename VARCHAR(10);
BEGIN
newcableno=0;
SELECT INTO cnmax count(cableno) FROM cablelist WHERE projectcodeid = pcid;
WHILE newcableno <= cnmax LOOP
newcableno=newcableno+1;
-- Grote getallen dan 9998 worden niet toegstaan omdat de lpad functie
-- ook een truncate doet
IF newcableno > 998 THEN
RETURN NULL;
END IF;
-- Controleer op prefix, zo ja gebruik deze
IF prefix != '''' THEN
cablename = prefix || ''.'' || lpad(newcableno, 3, ''0'');
ELSE
cablename = lpad(newcableno, 3, ''0'');
END IF;
-- Controleer of dit nummer al bestaad
SELECT INTO CHECK * FROM cablelist WHERE projectcodeid = pcid AND
cableno=cablename;
IF NOT FOUND THEN
RETURN cablename;
END IF;
END LOOP;
-- Niets gevonden, hier zouden we normaal gesproken niet
-- komen. Dit omdat we <= testen en dis altijd cnmax+1 testen
RETURN NULL;
-- Alle nummer zijn in gebruik, kies nu een volgt nummer
-- newcableno zal ALTIJD <= 998 zijn en dus altijd maar 3 positie innemen
newcableno=newcableno+1;
IF prefix != '''' THEN
cablename = prefix || ''.'' || lpad(newcableno, 3, ''0'');
ELSE
cablename = lpad(newcableno, 3, ''0'');
END IF;
RETURN cableno;
END;
' LANGUAGE 'plpgsql';
> -----Oorspronkelijk bericht-----
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]Namens Tomasz Myrta
> Verzonden: woensdag 22 januari 2003 16:46
> Aan: Ries van Twisk
> CC: [EMAIL PROTECTED]
> Onderwerp: Re: [SQL] To use a VIEW or not to use a View.....
>
>
> Ries van Twisk wrote:
>
> >Dear PostgreSQL users,
> >
> >I have a view and a table,
> >
> >I understand that when a frontend accesses a VIEW that
> PostgreSQL cannot use
> >a index on that view.
> >For example when I do this: SELECT * FROM full_cablelist WHERE
> >projectocode=5; Correct?
> >
> >Now I just want to make sure for myself if the VIEW I
> created is the right
> >way to go, or is it better
> >to contruct a SQL in my application that looks like the view
> and send it to
> >postgreSQL so it will
> >use all indexes correctly. I use postgreSQL 7.2.1
> >
> >I beliefe there is a change in postgreSQL 7.3.x on which I
> can cache a
> >view??? Not sure what the issue was.
> >
> >I ask this because I expect pore performance in feature when
> the cablelist
> >table holds up to around 20.000 rows.
> >Each query to full_cablelist will return around 1200 rows.
> >
> >best regards,
> >Ries van Twisk
> >
> >
> >
> >-- CABLE LIST
> >CREATE TABLE cablelist (
> > id SERIAL,
> > cableno VARCHAR(8),
> > projectcodeid INTEGER CONSTRAINT
> cablelist_projectcodes_con NOT NULL
> >REFERENCES projectcodes(id) ON DELETE CASCADE,
> > fromconnid INTEGER CONSTRAINT
> cablelist_fromconnid_con NOT NULL
> >REFERENCES libconnections(id) ON DELETE CASCADE,
> > toconnid INTEGER CONSTRAINT
> cablelist_toconnid_con NOT NULL REFERENCES
> >libconnections(id) ON DELETE CASCADE,
> > fromshiplocationid INTEGER CONSTRAINT
> cablelist_fromshiplocationid_con
> >NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> > toshiplocationid INTEGER CONSTRAINT
> cablelist_toshiplocationid_con NOT
> >NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> > marktypesid INTEGER CONSTRAINT
> cablelist_tomarktypeid_con NOT NULL
> >REFERENCES marktypes(id) ON DELETE CASCADE,
> > cabletypeid INTEGER CONSTRAINT
> cablelist_cabletypeid_con NOT NULL
> >REFERENCES cabletypes(id) ON DELETE CASCADE,
> > cut BOOLEAN DEFAULT 'false' NOT NULL,
> > placed BOOLEAN DEFAULT 'false' NOT NULL,
> > ok BOOLEAN DEFAULT 'false'
> >);
> >
> >
> >-- CABLE LIST VIEW
> >CREATE VIEW full_cablelist AS
> >SELECT cl.id,
> >cl.cableno AS cableno,
> >pc.projectcode AS projectcode,
> >pc.id AS projectcodeid,
> >lcf.name AS fconnection, lct.name AS tconnection,
> >lif.name AS fitem, lit.name AS titem,
> >slf.rib AS frib,slt.rib AS trib,
> >slf.name AS fname, slt.name AS tname,
> >ct.cabletype AS cabletype, ct.coretype AS coretype,
> >cl.cut,
> >cl.placed,
> >cl.ok
> >
> >FROM cablelist AS cl,
> >libconnections AS lcf, libconnections AS lct,
> >libitems AS lif, libitems AS lit,
> >shiplocations AS slf, shiplocations AS slt,
> >projectcodes AS pc,
> >cabletypes AS ct
> >
> >WHERE
> >pc.id=cl.projectcodeid AND
> >lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
> >lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
> >slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
> >ct.id=cl.cabletypeid
>
> How can we help you with table indexing, if you didn't write anything
> about indexes you have already created on your tables? Anyway
> you don't need
> indexes on a view, but indexes on your tables. You need also
> a well constructed
> view.
>
> For your query:
> - make sure, you have index on projectcodes(projectcode) - if
> you have many projectcodes
> and index on cablelist(projectcodeid)
> - make sure, you did "vacuum analyze" before you test your query.
> - send result of "explain analyze SELECT * FROM full_cablelist WHERE
> projectocode=5" to us.
>
> Anyway result can't be too fast, because query returns >1000
> rows which is rather
> a big amount of data.
>
> Regards,
> Tomasz Myrta
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> [EMAIL PROTECTED])
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
