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