Re: Slow running query with views...how to increase efficiency? with index? - Mailing list pgsql-general

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.