Re: Question about query optimization

On 11/15/06, Matthias(dot)Pitzl(at)izb(dot)de <Matthias(dot)Pitzl(at)izb(dot)de> wrote:
>
> Hello Gurjeet!
>
> Tried your suggestion but this is just a marginal improvement.
> Our query needs 126 ms time, your query 110 ms.
>

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.00rows=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.01rows=100000 w
idth=4)
-> Index Scan using ind_t_b on t t2 (cost=0.00..3148.01rows=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.03rows=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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com