Thread: Slow query

Slow query

From
Yonatan Goraly
Date:
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;



Re: Slow query

From
Alvaro Herrera
Date:
On Mon, Oct 27, 2003 at 12:08:31AM +0200, Yonatan Goraly wrote:
> 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?

Say, have you ANALYZEd your tables ?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

Re: Slow query

From
Yonatan Goraly
Date:
yes

Alvaro Herrera wrote:

>On Mon, Oct 27, 2003 at 12:08:31AM +0200, Yonatan Goraly wrote:
>
>
>>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?
>>
>>
>
>Say, have you ANALYZEd your tables ?
>
>
>