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

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_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'
 
);


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