Slow query - Mailing list pgsql-general
From | Yonatan Goraly |
---|---|
Subject | Slow query |
Date | |
Msg-id | 3F9C45DF.2090609@sbcglobal.net Whole thread Raw |
Responses |
Re: Slow query
(Alvaro Herrera <alvherre@dcc.uchile.cl>)
|
List | pgsql-general |
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;
pgsql-general by date: