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

Hi,
I'm a new user...
First of all excuse me for the bad english... :confused:
I have a great problem!
I have to do some little query with views, but the views contain thousand
and thousand of records.
Searching online I have found somthing about view index, but I don't know
the right syntax for PostgreSQL.
I have tries this:
CREATE INDEX View1_index
  ON View1
  USING btree
  (term1);

It isn't correct because this syntax is for tables, instead View1 is a view.
Do you know the syntax to create view index?
thanks a lot
--
View this message in context:
http://www.nabble.com/Slow-running-query-with-views...how-to-increase-efficiency--with-index--tp26086104p26086104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

From
Alan Hodgson
Date:
On Tuesday 27 October 2009, fox7 <ale_shark7@yahoo.it> wrote:
> I have tries this:
> CREATE INDEX View1_index
>   ON View1
>   USING btree
>   (term1);
>
> It isn't correct because this syntax is for tables, instead View1 is a
> view. Do you know the syntax to create view index?
> thanks a lot

You can't create indexes on views. They will make use of appropriate indexes
on the underlying tables, however.

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."

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

From
Merlin Moncure
Date:
On Tue, Oct 27, 2009 at 5:11 PM, fox7 <ale_shark7@yahoo.it> wrote:
>
> Hi,
> I'm a new user...
> First of all excuse me for the bad english... :confused:
> I have a great problem!
> I have to do some little query with views, but the views contain thousand
> and thousand of records.
> Searching online I have found somthing about view index, but I don't know
> the right syntax for PostgreSQL.
> I have tries this:
> CREATE INDEX View1_index
>  ON View1
>  USING btree
>  (term1);

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)

merlin

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.


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

From
Alban Hertroys
Date:
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!





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.


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

From
Alban Hertroys
Date:
On 28 Oct 2009, at 13:42, fox7 wrote:
> What do you mean for analyze results?


http://www.postgresql.org/docs/8.4/interactive/sql-explain.html

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,4ae83f5911071064615400!



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

From
"A. Kretschmer"
Date:
In response to fox7 :
>
> What do you mean for analyze results?

Try "explain analyse select ..."

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

In your case: explain analyse select * from v2to where ...

Please read:
http://www.postgresql.org/docs/8.4/interactive/using-explain.html


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99


Alban Hertroys-3 wrote:
>
>> What do you mean for analyze results?
> http://www.postgresql.org/docs/8.4/interactive/sql-explain.html
>
thanks...

Now I try and put here the results...

However I'm using Postgre 8.3, not 8.4...
...but I don't think this is the problem!
--
View this message in context:
http://www.nabble.com/Slow-running-query-with-views...how-to-increase-efficiency--with-index--tp26086104p26094297.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


I copy the results derived by istruction "EXPLAIN ANALYZE" for the two
query...

----------------------Query without views-------------------------
"Unique  (cost=406.58..407.13 rows=73 width=114) (actual time=1.262..1.448
rows=40 loops=1)"
"  ->  Sort  (cost=406.58..406.77 rows=73 width=114) (actual
time=1.257..1.313 rows=40 loops=1)"
"        Sort Key: table_0.term1, table_1.term2"
"        Sort Method:  quicksort  Memory: 23kB"
"        ->  Append  (cost=302.63..404.32 rows=73 width=114) (actual
time=0.747..1.147 rows=40 loops=1)"
"              ->  Unique  (cost=302.63..302.99 rows=49 width=114) (actual
time=0.742..0.933 rows=40 loops=1)"
"                    ->  Sort  (cost=302.63..302.75 rows=49 width=114)
(actual time=0.737..0.795 rows=40 loops=1)"
"                          Sort Key: table_0.term1, table_1.term2"
"                          Sort Method:  quicksort  Memory: 23kB"
"                          ->  Nested Loop  (cost=0.00..301.25 rows=49
width=114) (actual time=0.088..0.477 rows=40 loops=1)"
"                                ->  Index Scan using TO_index1 on TO
table_1  (cost=0.00..15.81 rows=3 width=52) (actual time=0.046..0.050 rows=2
loops=1)"
"                                      Index Cond: ((term1)::text =
'c'::text)"
"                                ->  Index Scan using TC_index2 on TC
table_0  (cost=0.00..94.85 rows=24 width=111) (actual time=0.095..0.144
rows=20 loops=2)"
"                                      Index Cond: ((table_0.term2)::text =
(table_1.term2)::text)"
"              ->  Unique  (cost=100.42..100.60 rows=24 width=110) (actual
time=0.095..0.095 rows=0 loops=1)"
"                    ->  Sort  (cost=100.42..100.48 rows=24 width=110)
(actual time=0.091..0.091 rows=0 loops=1)"
"                          Sort Key: table_0.term1, table_1.term1"
"                          Sort Method:  quicksort  Memory: 17kB"
"                          ->  Nested Loop  (cost=0.00..99.87 rows=24
width=110) (actual time=0.060..0.060 rows=0 loops=1)"
"                                ->  Seq Scan on TB table_1
(cost=0.00..4.72 rows=1 width=48) (actual time=0.054..0.054 rows=0 loops=1)"
"                                      Filter: ((term2)::text = 'c'::text)"
"                                ->  Index Scan using TC_index2 on TC
table_0  (cost=0.00..94.85 rows=24 width=111) (never executed)"
"                                      Index Cond: ((table_0.term2)::text =
(table_1.term1)::text)"
"Total runtime: 1.641 ms"


----------------------Query with views-------------------------
"  ->  Sort  (cost=40863.02..40865.50 rows=994 width=436) (actual
time=5142.974..5143.026 rows=40 loops=1)"
"        Sort Key: TC.term1, v2TO.term2"
"        Sort Method:  quicksort  Memory: 23kB"
"        ->  Hash Join  (cost=38857.33..40813.53 rows=994 width=436) (actual
time=3547.557..5142.853 rows=40 loops=1)"
"              Hash Cond: ((TC.term2)::text = (v2TO.term2)::text)"
"              ->  Unique  (cost=38837.21..40099.83 rows=49719 width=111)
(actual time=3546.697..4869.647 rows=168340 loops=1)"
"                    ->  Sort  (cost=38837.21..39258.08 rows=168350
width=111) (actual time=3546.691..4363.092 rows=168350 loops=1)"
"                          Sort Key: TC.term1, TC.term2"
"                          Sort Method:  external merge  Disk: 21032kB"
"                          ->  Seq Scan on TC  (cost=0.00..4658.50
rows=168350 width=111) (actual time=0.010..294.459 rows=168350 loops=1)"
"              ->  Hash  (cost=20.07..20.07 rows=4 width=218) (actual
time=0.219..0.219 rows=2 loops=1)"
"                    ->  Subquery Scan v2TO  (cost=20.00..20.07 rows=4
width=218) (actual time=0.192..0.207 rows=2 loops=1)"
"                          ->  Unique  (cost=20.00..20.03 rows=4 width=108)
(actual time=0.186..0.195 rows=2 loops=1)"
"                                ->  Sort  (cost=20.00..20.01 rows=4
width=108) (actual time=0.182..0.185 rows=2 loops=1)"
"                                      Sort Key: TO.term1, TO.term2"
"                                      Sort Method:  quicksort  Memory:
17kB"
"                                      ->  Append  (cost=15.17..19.96 rows=4
width=108) (actual time=0.094..0.169 rows=2 loops=1)"
"                                            ->  Unique  (cost=15.17..15.19
rows=3 width=108) (actual time=0.090..0.100 rows=2 loops=1)"
"                                                  ->  Sort
(cost=15.17..15.18 rows=3 width=108) (actual time=0.086..0.088 rows=2
loops=1)"
"                                                        Sort Key: TO.term2"
"                                                        Sort Method:
quicksort  Memory: 17kB"
"                                                        ->  Bitmap Heap
Scan on TO  (cost=4.28..15.15 rows=3 width=108) (actual time=0.064..0.067
rows=2 loops=1)"
"                                                              Recheck Cond:
((term1)::text = 'c'::text)"
"                                                              ->  Bitmap
Index Scan on TO_index1  (cost=0.00..4.28 rows=3 width=0) (actual
time=0.052..0.052 rows=2 loops=1)"
"                                                                    Index
Cond: ((term1)::text = 'c'::text)"
"                                            ->  Seq Scan on TB
(cost=0.00..4.72 rows=1 width=104) (actual time=0.056..0.056 rows=0
loops=1)"
"                                                  Filter: ((term2)::text =
'c'::text)"
"Total runtime: 5147.410 ms"
--
View this message in context:
http://www.nabble.com/Slow-running-query-with-views...how-to-increase-efficiency--with-index--tp26086104p26094976.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

From
Alban Hertroys
Date:
On 28 Oct 2009, at 14:51, fox7 wrote:

>
> I copy the results derived by istruction "EXPLAIN ANALYZE" for the two
> query...

For a next time, if you attach that output as text files they won't
get wrapped by e-mail clients, making them a bit easier to read. Also,
this looks like output from pg_admin? Command-line psql doesn't wrap
the lines in quotes (those give problems in tools like http://explain-analyze.info/

> ----------------------Query with views-------------------------
> "  ->  Sort  (cost=40863.02..40865.50 rows=994 width=436) (actual
> time=5142.974..5143.026 rows=40 loops=1)"
> "        Sort Key: TC.term1, v2TO.term2"
> "        Sort Method:  quicksort  Memory: 23kB"
> "        ->  Hash Join  (cost=38857.33..40813.53 rows=994 width=436)
> (actual
> time=3547.557..5142.853 rows=40 loops=1)"
> "              Hash Cond: ((TC.term2)::text = (v2TO.term2)::text)"
> "              ->  Unique  (cost=38837.21..40099.83 rows=49719
> width=111)
> (actual time=3546.697..4869.647 rows=168340 loops=1)"
> "                    ->  Sort  (cost=38837.21..39258.08 rows=168350
> width=111) (actual time=3546.691..4363.092 rows=168350 loops=1)"
> "                          Sort Key: TC.term1, TC.term2"
> "                          Sort Method:  external merge  Disk:
> 21032kB"

Here's your problem. The time taken jumps from a few hundreds of
milliseconds to 3.5 seconds here.

Postgres is told to sort a largish dataset and it doesn't fit in
workmem, so it has to push it to disk. This may well be one of the
unnecessary orderings or distinct specifiers you put in your views,
I'd try removing some of those and see what happens.

Alternatively you can increase the amount of work_mem that's available
per connection.

> "                          ->  Seq Scan on TC  (cost=0.00..4658.50
> rows=168350 width=111) (actual time=0.010..294.459 rows=168350
> loops=1)"
> "              ->  Hash  (cost=20.07..20.07 rows=4 width=218) (actual
> time=0.219..0.219 rows=2 loops=1)"
> "                    ->  Subquery Scan v2TO  (cost=20.00..20.07 rows=4
> width=218) (actual time=0.192..0.207 rows=2 loops=1)"
> "                          ->  Unique  (cost=20.00..20.03 rows=4
> width=108)
> (actual time=0.186..0.195 rows=2 loops=1)"
> "                                ->  Sort  (cost=20.00..20.01 rows=4
> width=108) (actual time=0.182..0.185 rows=2 loops=1)"
> "                                      Sort Key: TO.term1, TO.term2"
> "                                      Sort Method:  quicksort
> Memory:
> 17kB"
> "                                      ->  Append
> (cost=15.17..19.96 rows=4
> width=108) (actual time=0.094..0.169 rows=2 loops=1)"
> "                                            ->  Unique
> (cost=15.17..15.19
> rows=3 width=108) (actual time=0.090..0.100 rows=2 loops=1)"
> "                                                  ->  Sort
> (cost=15.17..15.18 rows=3 width=108) (actual time=0.086..0.088 rows=2
> loops=1)"
> "                                                        Sort Key:
> TO.term2"
> "                                                        Sort Method:
> quicksort  Memory: 17kB"
> "                                                        ->  Bitmap
> Heap
> Scan on TO  (cost=4.28..15.15 rows=3 width=108) (actual
> time=0.064..0.067
> rows=2 loops=1)"
> "
> Recheck Cond:
> ((term1)::text = 'c'::text)"
> "                                                              ->
> Bitmap
> Index Scan on TO_index1  (cost=0.00..4.28 rows=3 width=0) (actual
> time=0.052..0.052 rows=2 loops=1)"
> "
> Index
> Cond: ((term1)::text = 'c'::text)"
> "                                            ->  Seq Scan on TB
> (cost=0.00..4.72 rows=1 width=104) (actual time=0.056..0.056 rows=0
> loops=1)"
> "                                                  Filter:
> ((term2)::text =
> 'c'::text)"
> "Total runtime: 5147.410 ms"

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4ae956d611071386765946!