Re: [GENERAL] Slow performance with no apparent reason

Yonatan Goraly Sun, 26 Oct 2003 01:33:38 -0700

I guess my first message was not accurate, since t1 is a view, that includes t2.

Attached are the real queries with their corresponding plans, the first one takes 10.8 sec to execute, the second one takes 0.6 sec.

To simplify, I expanded the view, so the attached query refers to tables only.

Martijn van Oosterhout wrote:

Please supply EXPLAIN ANALYZE output.

On Sun, Oct 26, 2003 at 12:25:37AM +0300, Yonatan Goraly wrote:

I am in the process of  adding PostgreSQL support for an application, in 
addition to Oracle and MS SQL.
I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board.


I have a query that generally looks like this:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string' 
AND t2.q=1

This query is strikingly slow (about 100 sec when both t1 and t2 has 
about 1,200 records, compare with less than 4 sec with MS SQL and Oracle)

The strange thing is that if I remove one of the last 2 conditions 
(doesn't matter which one), I get the same performance like with the 
other databases.
Since in this particular case both conditions ( t2.p='string', t2.q=1) 
are not required, I can't understand why having both turns the query so 
slow.
A query on table t2 alone is fast with or without the 2 conditions.

I tired several alternatives, this one works pretty well:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND
    EXISTS (
            SELECT * FROM t2 t2a WHERE t2a.p='string' AND t2a.q=1 AND 
t2a.y=t2.y )

Since the first query is simpler than the second, it seems to me like a bug.

Please advise

Yonatan

------------------------------------------------------------------------------------------------------------
slow query(10 sec):

select ent.ID,ent.TYPE,ent.STATUS,ent.NAME
        from  (select
   e.ID, e.TYPE, e.STATUS, e.NAME
from
   ENT_PROJECT e,
   (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 ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID
                          join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID
                          join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID
                          join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID
                          join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID
                          join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID
        ) path
        where e.STATUS!=cast(-1 as numeric)
           and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier
                                where hier.NODE_ID=ent.ID and 
hier.HIERARCHY_ID='IMPLEMENTATION' and hier.DOMAIN=1


------------------------------------------------------------------------------------------------------------
QUERY PLAN
Nested Loop  (cost=1808.05..1955.27 rows=14 width=660)
  Join Filter: ("outer".id = "inner".node_id)
  ->  Nested Loop  (cost=0.00..10.82 rows=1 width=244)
        ->  Index Scan using idx_hierarch_hierarch_id on comn_attr_hierarch hier  
(cost=0.00..5.98 rows=1 width=32)
              Index Cond: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND ("domain" = 
1::numeric))
        ->  Index Scan using pk_ent_project on ent_project e  (cost=0.00..4.83 rows=1 
width=212)
              Index Cond: ("outer".node_id = e.id)
              Filter: (status <> -1::numeric)
  ->  Materialize  (cost=1910.33..1910.33 rows=2730 width=416)
        ->  Merge Join  (cost=1808.05..1910.33 rows=2730 width=416)
              Merge Cond: ("outer".id = "inner".folder_id_6)
              ->  Index Scan using pk_ent_folder on ent_folder f6  (cost=0.00..52.00 
rows=1000 width=32)
              ->  Sort  (cost=1808.05..1814.88 rows=2730 width=384)
                    Sort Key: h.folder_id_6
                    ->  Merge Join  (cost=1275.45..1377.73 rows=2730 width=384)
                          Merge Cond: ("outer".id = "inner".folder_id_5)
                          ->  Index Scan using pk_ent_folder on ent_folder f5  
(cost=0.00..52.00 rows=1000 width=32)
                          ->  Sort  (cost=1275.45..1282.28 rows=2730 width=352)
                                Sort Key: h.folder_id_5
                                ->  Merge Join  (cost=1017.37..1119.64 rows=2730 
width=352)
                                      Merge Cond: ("outer".id = "inner".folder_id_4)
                                      ->  Index Scan using pk_ent_folder on ent_folder 
f4  (cost=0.00..52.00 rows=1000 width=32)
                                      ->  Sort  (cost=1017.37..1024.19 rows=2730 
width=320)
                                            Sort Key: h.folder_id_4
                                            ->  Merge Join  (cost=759.28..861.56 
rows=2730 width=320)
                                                  Merge Cond: ("outer".id = 
"inner".folder_id_3)
                                                  ->  Index Scan using pk_ent_folder 
on ent_folder f3  (cost=0.00..52.00 rows=1000 width=32)
                                                  ->  Sort  (cost=759.28..766.11 
rows=2730 width=288)
                                                        Sort Key: h.folder_id_3
                                                        ->  Merge Join  
(cost=501.20..603.47 rows=2730 width=288)
                                                              Merge Cond: ("outer".id 
= "inner".folder_id_2)
                                                              ->  Index Scan using 
pk_ent_folder on ent_folder f2  (cost=0.00..52.00 rows=1000 width=32)
                                                              ->  Sort  
(cost=501.20..508.02 rows=2730 width=256)
                                                                    Sort Key: 
h.folder_id_2
                                                                    ->  Merge Join  
(cost=243.11..345.39 rows=2730 width=256)
                                                                          Merge Cond: 
("outer".id = "inner".folder_id_1)
                                                                          ->  Index 
Scan using pk_ent_folder on ent_folder f1  (cost=0.00..52.00 rows=1000 width=32)
                                                                          ->  Sort  
(cost=243.11..249.94 rows=2730 width=224)
                                                                                Sort 
Key: h.folder_id_1
                                                                                ->  
Seq Scan on comn_attr_hierarch h  (cost=0.00..87.30 rows=2730 width=224)


------------------------------------------------------------------------------------------------------------
Fast query (.6 sec):

select ent.ID,ent.TYPE,ent.STATUS,ent.NAME
        from  (select
   e.ID, e.TYPE, e.STATUS, e.NAME
from
   ENT_PROJECT e,
   (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 ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID
                          join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID
                          join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID
                          join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID
                          join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID
                          join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID
        ) path
        where e.STATUS!=cast(-1 as numeric)
           and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier
                                where hier.NODE_ID=ent.ID and exists(
                        select * from COMN_ATTR_HIERARCH h2 where 
h2.HIERARCHY_ID='IMPLEMENTATION' and h2.DOMAIN=1 and h2.NODE_ID=hier.NODE_ID
                                and h2.HIERARCHY_ID=hier.HIERARCHY_ID and 
h2.DOMAIN=hier.DOMAIN)


------------------------------------------------------------------------------------------------------------
QUERY PLAN
Merge Join  (cost=16145.60..16289.84 rows=18539 width=660)
  Merge Cond: ("outer".id = "inner".node_id)
  ->  Merge Join  (cost=13782.29..13863.08 rows=1358 width=244)
        Merge Cond: ("outer".id = "inner".node_id)
        ->  Index Scan using pk_ent_project on ent_project e  (cost=0.00..54.50 
rows=995 width=212)
              Filter: (status <> -1::numeric)
        ->  Sort  (cost=13782.29..13785.70 rows=1365 width=32)
              Sort Key: hier.node_id
              ->  Seq Scan on comn_attr_hierarch hier  (cost=0.00..13711.21 rows=1365 
width=32)
                    Filter: (subplan)
                    SubPlan
                      ->  Index Scan using pk_comn_attr_hierarch on comn_attr_hierarch 
h2  (cost=0.00..4.99 rows=1 width=316)
                            Index Cond: (("domain" = 1::numeric) AND ("domain" = $2) 
AND (node_id = $0))
                            Filter: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND 
(hierarchy_id = $1))
  ->  Sort  (cost=2363.32..2370.14 rows=2730 width=416)
        Sort Key: h.node_id
        ->  Merge Join  (cost=1808.05..1910.33 rows=2730 width=416)
              Merge Cond: ("outer".id = "inner".folder_id_6)
              ->  Index Scan using pk_ent_folder on ent_folder f6  (cost=0.00..52.00 
rows=1000 width=32)
              ->  Sort  (cost=1808.05..1814.88 rows=2730 width=384)
                    Sort Key: h.folder_id_6
                    ->  Merge Join  (cost=1275.45..1377.73 rows=2730 width=384)
                          Merge Cond: ("outer".id = "inner".folder_id_5)
                          ->  Index Scan using pk_ent_folder on ent_folder f5  
(cost=0.00..52.00 rows=1000 width=32)
                          ->  Sort  (cost=1275.45..1282.28 rows=2730 width=352)
                                Sort Key: h.folder_id_5
                                ->  Merge Join  (cost=1017.37..1119.64 rows=2730 
width=352)
                                      Merge Cond: ("outer".id = "inner".folder_id_4)
                                      ->  Index Scan using pk_ent_folder on ent_folder 
f4  (cost=0.00..52.00 rows=1000 width=32)
                                      ->  Sort  (cost=1017.37..1024.19 rows=2730 
width=320)
                                            Sort Key: h.folder_id_4
                                            ->  Merge Join  (cost=759.28..861.56 
rows=2730 width=320)
                                                  Merge Cond: ("outer".id = 
"inner".folder_id_3)
                                                  ->  Index Scan using pk_ent_folder 
on ent_folder f3  (cost=0.00..52.00 rows=1000 width=32)
                                                  ->  Sort  (cost=759.28..766.11 
rows=2730 width=288)
                                                        Sort Key: h.folder_id_3
                                                        ->  Merge Join  
(cost=501.20..603.47 rows=2730 width=288)
                                                              Merge Cond: ("outer".id 
= "inner".folder_id_2)
                                                              ->  Index Scan using 
pk_ent_folder on ent_folder f2  (cost=0.00..52.00 rows=1000 width=32)
                                                              ->  Sort  
(cost=501.20..508.02 rows=2730 width=256)
                                                                    Sort Key: 
h.folder_id_2
                                                                    ->  Merge Join  
(cost=243.11..345.39 rows=2730 width=256)
                                                                          Merge Cond: 
("outer".id = "inner".folder_id_1)
                                                                          ->  Index 
Scan using pk_ent_folder on ent_folder f1  (cost=0.00..52.00 rows=1000 width=32)
                                                                          ->  Sort  
(cost=243.11..249.94 rows=2730 width=224)
                                                                                Sort 
Key: h.folder_id_1
                                                                                ->  
Seq Scan on comn_attr_hierarch h  (cost=0.00..87.30 rows=2730 width=224)

------------------------------------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Re: [GENERAL] Slow performance with no apparent reason

Reply via email to