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

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.