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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
