Re: To use a VIEW or not to use a View..... - Mailing list pgsql-sql

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.37rows=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
shiplocationsslt  (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 OPAQUEAS '/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 '
DECLAREcheck     RECORD;
BEGINSELECT 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_cabletypesBEFORE INSERT OR UPDATE ON cabletypes FOR EACH ROWEXECUTE 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 '
DECLAREcheck RECORD;
BEGIN-- Update van de name mag welIF TG_OP = ''UPDATE'' THEN    IF NEW.projectcodeid = OLD.projectcodeid AND NEW.name =
OLD.nameTHEN           RETURN NEW;    END IF;END IF;
 
-- Controleer of the combinatie projectcode en ribnummer unique isSELECT INTO check * FROM libitems WHERE
projectcodeid=new.projectcodeidAND
 
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_linitemsBEFORE INSERT OR UPDATE ON libitems FOR EACH ROWEXECUTE 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 '
DECLAREcheck     RECORD;
BEGIN-- Update van de name mag welIF TG_OP = ''UPDATE'' THEN    IF NEW.libitemid = OLD.libitemid AND NEW.name =
OLD.nameTHEN           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_libconnectionsBEFORE INSERT OR UPDATE ON libconnections FOR EACH ROWEXECUTE 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
2THEN '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_conNOT 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_conNOT
 
NULL REFERENCES shiplocations(id) ON DELETE CASCADE,   marktypesid        INTEGER CONSTRAINT cablelist_tomarktypeid_con
NOTNULL
 
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 '
DECLAREpcid        ALIAS FOR $1;prefix        ALIAS FOR $2;
cnmax        INTEGER;newcableno    INTEGER;CHECK        RECORD;cablename    VARCHAR(10);
BEGINnewcableno=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
testenRETURNNULL;
 
-- Alle nummer zijn in gebruik, kies nu een volgt nummer-- newcableno zal ALTIJD <= 998 zijn en dus altijd maar 3
positieinnemennewcableno=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: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Tomasz Myrta
> Verzonden: woensdag 22 januari 2003 16:46
> Aan: Ries van Twisk
> CC: pgsql-sql@postgresql.org
> 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
> majordomo@postgresql.org)
>