On 28 Oct 2009, at 9:57, fox7 wrote:
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)
You forgot to show us the most important part.
*) 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'
If you're using a UNION you can drop the DISTINCTs, as the results of
UNION are guaranteed to be distinct. If you don't want that, use UNION
ALL instead.
---------Definition of tables and views involved-------------
-- View: v2TC
CREATE OR REPLACE VIEW v2TC AS
SELECT DISTINCT TC.term1, TC.term2
FROM TC
ORDER BY TC.term1, TC.term2;
-- 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;
Do you absolutely need to order the output of your views? You could
just order the results of your queries on your views instead. The way
you do it now the database needs to order results always, even if the
order doesn't actually matter to you. I suspect this is part of why
your query is slow.
Besides that, the order of your V2TO view is going to be determined by
the UNION clause anyway, as it needs to sort the results of the union
to make them unique. The order by in the first subquery of that view
can safely be removed I think.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4ae823b911071766412181!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
