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