I have a query that uses the same view 6 times. It seems that the
database engine is calculating the view each time.
The result is very poor performance. The same query takes 2 sec with MS
SQL, and more than 10 sec with PostgreSQL.
Is there a method to improve the performance besides merging the
components of the view into one table?
I believe that caching query results could have resolved this issue
Yonatan Goraly
This is the query:
select h.*,
CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME ||
CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME ||
CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME ||
CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME ||
CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME ||
CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END
END END END as PATH
from COMN_ATTR_HIERARCH h
join ALL_FOLDERS_VIEW_NAME f1 on h.FOLDER_ID_1=f1.ID
join ALL_FOLDERS_VIEW_NAME f2 on h.FOLDER_ID_2=f2.ID
join ALL_FOLDERS_VIEW_NAME f3 on h.FOLDER_ID_3=f3.ID
join ALL_FOLDERS_VIEW_NAME f4 on h.FOLDER_ID_4=f4.ID
join ALL_FOLDERS_VIEW_NAME f5 on h.FOLDER_ID_5=f5.ID
join ALL_FOLDERS_VIEW_NAME f6 on h.FOLDER_ID_6=f6.ID;
------------------------------------------------------------------
QUERY PLAN
Nested Loop (cost=421.29..426.60 rows=1 width=511)
Join Filter: ("outer".folder_id_6 = "inner".id)
-> Nested Loop (cost=400.22..403.10 rows=1 width=447)
Join Filter: ("outer".folder_id_5 = "inner".id)
-> Merge Join (cost=379.16..379.35 rows=1 width=383)
Merge Cond: ("outer".folder_id_4 = "inner".id)
-> Sort (cost=355.34..355.36 rows=7 width=319)
Sort Key: h.folder_id_4
-> Merge Join (cost=354.74..355.23 rows=7 width=319)
Merge Cond: ("outer".folder_id_3 = "inner".id)
-> Sort (cost=330.92..331.05 rows=53 width=255)
Sort Key: h.folder_id_3
-> Merge Join (cost=326.71..329.41
rows=53 width=255)
Merge Cond: ("outer".folder_id_2 =
"inner".id)
-> Sort (cost=302.89..303.84
rows=380 width=191)
Sort Key: h.folder_id_2
-> Merge Join
(cost=268.08..286.62 rows=380 width=191)
Merge Cond:
("outer".folder_id_1 = "inner".id)
-> Sort
(cost=244.26..251.09 rows=2732 width=127)
Sort Key:
h.folder_id_1
-> Seq Scan on
comn_attr_hierarch h (cost=0.00..88.32 rows=2732 width=127)
-> Sort
(cost=23.82..23.89 rows=28 width=36)
Sort Key: f1.id
-> Subquery
Scan f1 (cost=21.07..23.15 rows=28 width=36)
->
Unique (cost=21.07..23.15 rows=28 width=36)
->
Sort (cost=21.07..21.76 rows=278 width=36)
Sort Key: id name
-> Append (cost=0.00..9.78 rows=278 width=36)
-> Subquery Scan "*SELECT* 1" (cost=0.00..3.15 rows=115 width=26)
-> Seq Scan on ent_folder (cost=0.00..3.15 rows=115 width=26)
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.25 rows=25 width=25)
-> Seq Scan on ent_mkt_segment (cost=0.00..1.25 rows=25 width=25)
-> Subquery Scan "*SELECT* 3" (cost=0.00..3.26 rows=126 width=24)
-> Seq Scan on ent_company (cost=0.00..3.26 rows=126 width=24)
-> Subquery Scan "*SELECT* 4" (cost=0.00..1.11 rows=11 width=25)
-> Seq Scan on ent_product (cost=0.00..1.11 rows=11 width=25)
-> Subquery Scan "*SELECT* 5" (cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on ent_environment_type (cost=0.00..1.01 rows=1 width=36)
-> Sort (cost=23.82..23.89
rows=28 width=36)
Sort Key: f2.id
-> Subquery Scan f2
(cost=21.07..23.15 rows=28 width=36)
-> Unique
(cost=21.07..23.15 rows=28 width=36)
-> Sort
(cost=21.07..21.76 rows=278 width=36)
Sort Key:
id name
->
Append (cost=0.00..9.78 rows=278 width=36)
->
Subquery Scan "*SELECT* 1" (cost=0.00..3.15 rows=115 width=26)
-> Seq Scan on ent_folder (cost=0.00..3.15 rows=115 width=26)
->
Subquery Scan "*SELECT* 2" (cost=0.00..1.25 rows=25 width=25)
-> Seq Scan on ent_mkt_segment (cost=0.00..1.25 rows=25 width=25)
->
Subquery Scan "*SELECT* 3" (cost=0.00..3.26 rows=126 width=24)
-> Seq Scan on ent_company (cost=0.00..3.26 rows=126 width=24)
->
Subquery Scan "*SELECT* 4" (cost=0.00..1.11 rows=11 width=25)
-> Seq Scan on ent_product (cost=0.00..1.11 rows=11 width=25)
->
Subquery Scan "*SELECT* 5" (cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on ent_environment_type (cost=0.00..1.01 rows=1 width=36)
-> Sort (cost=23.82..23.89 rows=28 width=36)
Sort Key: f3.id
-> Subquery Scan f3 (cost=21.07..23.15
rows=28 width=36)
-> Unique (cost=21.07..23.15
rows=28 width=36)
-> Sort (cost=21.07..21.76
rows=278 width=36)
Sort Key: id name
-> Append
(cost=0.00..9.78 rows=278 width=36)
-> Subquery
Scan "*SELECT* 1" (cost=0.00..3.15 rows=115 width=26)
-> Seq
Scan on ent_folder (cost=0.00..3.15 rows=115 width=26)
-> Subquery
Scan "*SELECT* 2" (cost=0.00..1.25 rows=25 width=25)
-> Seq
Scan on ent_mkt_segment (cost=0.00..1.25 rows=25 width=25)
-> Subquery
Scan "*SELECT* 3" (cost=0.00..3.26 rows=126 width=24)
-> Seq
Scan on ent_company (cost=0.00..3.26 rows=126 width=24)
-> Subquery
Scan "*SELECT* 4" (cost=0.00..1.11 rows=11 width=25)
-> Seq
Scan on ent_product (cost=0.00..1.11 rows=11 width=25)
-> Subquery
Scan "*SELECT* 5" (cost=0.00..1.01 rows=1 width=36)
-> Seq
Scan on ent_environment_type (cost=0.00..1.01 rows=1 width=36)
-> Sort (cost=23.82..23.89 rows=28 width=36)
Sort Key: f4.id
-> Subquery Scan f4 (cost=21.07..23.15 rows=28
width=36)
-> Unique (cost=21.07..23.15 rows=28 width=36)
-> Sort (cost=21.07..21.76 rows=278
width=36)
Sort Key: id name
-> Append (cost=0.00..9.78
rows=278 width=36)
-> Subquery Scan "*SELECT*
1" (cost=0.00..3.15 rows=115 width=26)
-> Seq Scan on
ent_folder (cost=0.00..3.15 rows=115 width=26)
-> Subquery Scan "*SELECT*
2" (cost=0.00..1.25 rows=25 width=25)
-> Seq Scan on
ent_mkt_segment (cost=0.00..1.25 rows=25 width=25)
-> Subquery Scan "*SELECT*
3" (cost=0.00..3.26 rows=126 width=24)
-> Seq Scan on
ent_company (cost=0.00..3.26 rows=126 width=24)
-> Subquery Scan "*SELECT*
4" (cost=0.00..1.11 rows=11 width=25)
-> Seq Scan on
ent_product (cost=0.00..1.11 rows=11 width=25)
-> Subquery Scan "*SELECT*
5" (cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on
ent_environment_type (cost=0.00..1.01 rows=1 width=36)
-> Subquery Scan f5 (cost=21.07..23.15 rows=28 width=36)
-> Unique (cost=21.07..23.15 rows=28 width=36)
-> Sort (cost=21.07..21.76 rows=278 width=36)
Sort Key: id name
-> Append (cost=0.00..9.78 rows=278 width=36)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..3.15 rows=115 width=26)
-> Seq Scan on ent_folder
(cost=0.00..3.15 rows=115 width=26)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..1.25 rows=25 width=25)
-> Seq Scan on ent_mkt_segment
(cost=0.00..1.25 rows=25 width=25)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..3.26 rows=126 width=24)
-> Seq Scan on ent_company
(cost=0.00..3.26 rows=126 width=24)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..1.11 rows=11 width=25)
-> Seq Scan on ent_product
(cost=0.00..1.11 rows=11 width=25)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on
ent_environment_type (cost=0.00..1.01 rows=1 width=36)
-> Subquery Scan f6 (cost=21.07..23.15 rows=28 width=36)
-> Unique (cost=21.07..23.15 rows=28 width=36)
-> Sort (cost=21.07..21.76 rows=278 width=36)
Sort Key: id name
-> Append (cost=0.00..9.78 rows=278 width=36)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..3.15 rows=115 width=26)
-> Seq Scan on ent_folder
(cost=0.00..3.15 rows=115 width=26)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..1.25 rows=25 width=25)
-> Seq Scan on ent_mkt_segment
(cost=0.00..1.25 rows=25 width=25)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..3.26 rows=126 width=24)
-> Seq Scan on ent_company
(cost=0.00..3.26 rows=126 width=24)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..1.11 rows=11 width=25)
-> Seq Scan on ent_product
(cost=0.00..1.11 rows=11 width=25)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on ent_environment_type
(cost=0.00..1.01 rows=1 width=36)
------------------------------------------------------------------
This is the view:
create view ALL_FOLDERS_VIEW_NAME as
select ID, NAME
from ENT_FOLDER
union
select ID, NAME
from ENT_MKT_SEGMENT
union
select ID, NAME
from ENT_COMPANY
union
select ID, NAME
from ENT_PRODUCT
union
select ID, NAME
from ENT_ENVIRONMENT_TYPE;
