From: Ries van Twisk <ries@jongert.nl>
To: pgsql-sql@postgresql.org
Subject: To use a VIEW or not to use a View.....
Date: Wed, 22 Jan 2003 16:12:52 +0100
Message-ID: <002001c2c228$bc3d2780$f100000a@IT001> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ries@jongert.nl
Subject: Re: To use a VIEW or not to use a View.....
In-Reply-To: <002001c2c228$bc3d2780$f100000a@IT001>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox