Re: Slow running query with views...how to increase efficiency? with index?

Views do not help or hurt performance. Views encapsulate complex queries.

If you have a slow running query, the usual way to get help is to post:
*) explain analyze results (most important)
*) the query (important)
*) interesting tables/indexes (somewhat important)
----------------------

These are 2 queries for example...
The first runs with 55ms, the 2nd with views is executed in 4500ms...
:confused:

SELECT DISTINCT table_0.term1, table_1.term2 FROM TC table_0 , TO table_1
WHERE table_1.term1='c' AND table_0.term2=table_1.term2
UNION
SELECT DISTINCT table_0.term1, table_1.term1 FROM TC table_0 , TB table_1
WHERE table_0.term2=table_1.term1 AND table_1.term2='c'

----------------------------------------------------------

SELECT DISTINCT V2TC.term1 AS term1,V2TO.term2 AS term2
FROM V2TO,V2TC
WHERE V2TO.term2=V2TC.term2 AND V2TO.term1='c'

---------Definition of tables and views involved-------------

-- Table: TC
CREATE TABLE TC(
term1 character varying(100),
term2 character varying(100)
)
WITH (OIDS=FALSE);
ALTER TABLE TC OWNER TO postgres;

-- Index: TC_index1
CREATE INDEX TC_index1
ON TC
USING btree
(term1);

-- Index: TC_index2
CREATE INDEX TC_index2
ON TC
USING btree
(term2);

--TO and TB are more or less equal to TC

-- View: v2TC
CREATE OR REPLACE VIEW v2TC AS
SELECT DISTINCT TC.term1, TC.term2
FROM TC
ORDER BY TC.term1, TC.term2;

ALTER TABLE v2TC OWNER TO postgres;

-- View: v2TO
CREATE OR REPLACE VIEW v2TO AS ( SELECT DISTINCT TO.term1, TO.term2
FROM TO
ORDER BY TO.term1, TO.term2)
UNION
SELECT TB.term2 AS term1, TB.term1 AS term2
FROM TB;

ALTER TABLE v2TO OWNER TO postgres;

--
View this message in context: http://www.nabble.com/Slow-running-query-with-views...how-to-increase-efficiency--with-index--tp26086104p26091310.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.