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.
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!