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

Alban Hertroys-3 wrote:
>
> On 28 Oct 2009, at 9:57, fox7 wrote:
>
> You forgot to show us the most important part.
> ---
> 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.
>
>

What do you mean for analyze results?

I create views by means of jdbc...
For example I have created V2TO as:
CREATE VIEW v2TO AS (
SELECT DISTINCT TO.term1, TO.term2
FROM TO
UNION
SELECT TB.term2 AS term1, TB.term1 AS term2
FROM TB;
)

The following format is like it appear selecting the view in Postgre...
> 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;

The problem is the execution time of the query with view...the first is ok!
--
View this message in context:
http://www.nabble.com/Slow-running-query-with-views...how-to-increase-efficiency--with-index--tp26086104p26093967.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.