Re: Question about query optimization

On 11/15/06, Matthias.Pitzl@izb.de <Matthias.Pitzl@izb.de> wrote:
I do not see an index access on the component table.... Do you have an index on component.component_id? Even a non-unique index will be of great help.

Correcting my previous mistake: Here's a query that looks more or less like that of yours. T1 is your component table, t2 id comp_hist and t3 is again comp_hist. And, as can be seen from the plan, ind_t_b is used for all these three aliases. What this means for you is that, create index es on component_id columns of both these tables.

The cost with an index on B is 440 times less than without it.

postgres=# explain
postgres-# select       count(*)
postgres-# from t as t1,
postgres-#      t as t2
postgres-# where        t1.b = t2.b
postgres-# and  t2.a =  (select max(a)
postgres(#              from    t as t3
postgres(#              where   t3.b = t1.b )
postgres-# ;
                                      QUERY PLAN

--------------------------------------------------------------------------------
-------
 Aggregate  (cost=358227614.66..358227614.67 rows=1 width=0)
   ->  Merge Join  (cost= 23114.64..358227614.65 rows=1 width=0)
         Merge Cond: (("outer"."?column2?" = t2.a) AND (t1.b = t2.b))
         ->  Sort  (cost=11557.32..11807.32 rows=100000 width=4)
               Sort Key: (subplan), t1.b
               ->  Seq Scan on t t1  (cost=0.00..1541.00 rows=100000 width=4)
                     SubPlan
                       ->  Aggregate  (cost=1791.01..1791.02 rows=1 width=4)
                             ->  Seq Scan on t t3  (cost= 0.00..1791.00 rows=1 wi
dth=4)
                                   Filter: (b = $0)
         ->  Sort  (cost=11557.32..11807.32 rows=100000 width=8)
               Sort Key: t2.a, t2.b
               ->  Seq Scan on t t2  (cost= 0.00..1541.00 rows=100000 width=8)
(13 rows)

postgres=# \e
postgres=# create index ind_t_a on t(a); create index ind_t_b on t(b);
CREATE INDEX
CREATE INDEX
postgres=# explain
postgres-# select       count(*)
postgres-# from t as t1,
postgres-#      t as t2
postgres-# where        t1.b = t2.b
postgres-# and  t2.a =  (select max(a)
postgres(#              from    t as t3
postgres(#              where   t3.b = t1.b )
postgres-# ;
                                       QUERY PLAN

--------------------------------------------------------------------------------
--------
 Aggregate  (cost=812400.03.. 812400.04 rows=1 width=0)
   ->  Merge Join  (cost=0.00..812400.02 rows=1 width=0)
         Merge Cond: (t1.b = t2.b)
         Join Filter: (t2.a = (subplan))
         ->  Index Scan using ind_t_b on t t1  (cost= 0.00..3148.01 rows=100000 w
idth=4)
         ->  Index Scan using ind_t_b on t t2  (cost=0.00..3148.01 rows=100000 w
idth=8)
         SubPlan
           ->  Aggregate  (cost=8.03..8.04 rows=1 width=4)
                 ->  Index Scan using ind_t_b on t t3  (cost=0.00..8.03 rows=1 w
idth=4)
                       Index Cond: (b = $0)
(10 rows)

postgres=# select       count(*)
postgres-# from t as t1,
postgres-#      t as t2
postgres-# where        t1.b = t2.b
postgres-# and  t2.a =  (select max(a)
postgres(#              from    t as t3
postgres(#              where   t3.b = t1.b )
postgres-# ;
 count
--------
 100000
(1 row)

Time: 1500.000 ms
postgres=#

Hope this helps.

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet @{ gmail | hotmail | yahoo }.com