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

[fox7 <ale_shark7@xxxxxxxx>]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general