Re: another query optimization question

Hi,


On 30.01.2004, at 19:10, Stephan Szabo wrote:

> On Fri, 30 Jan 2004, David Teran wrote:
>
> > select
> > sum(job_property_difference(t0.int_value, t1.int_value)) as rank
> >    from
> >    job_property t0,
> >    job_property t1
> >    where
> >    t0.id_job_profile = 911
> >    and t0.id_job_attribute = t1.id_job_attribute
> >    and t1.id_job_profile in (select id_job_profile from unemployed)
> >    and t1.id_job_profile <> 911;
> >
> > results in a query plan result:
>
> Can we see explain analyze output for the query, it'll give more
> information about actual time and row counts than plain explain.


sure, here it is comes. What we need to achieve is: we have different  
job_profiles, each profile has multiple values. For one given profile  
we need the ' sum of the distance of every value in the given profile  
and every other profile'. The result is usually grouped by the profile  
id but to make the query easier i removed this, it does not cost too  
much time and it turned out that this query here uses most of the time.

thanks, David



                                                                         
           QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
--------------------------------
 Aggregate  (cost=2689349.81..2689349.81 rows=1 width=8) (actual  
time=100487.423..100487.423 rows=1 loops=1)
   ->  Merge Join  (cost=2451266.53..2655338.83 rows=13604393 width=8)  
(actual time=82899.466..-2371037.726 rows=2091599 loops=1)
         Merge Cond: ("outer".id_job_attribute =  
"inner".id_job_attribute)
         ->  Sort  (cost=97.43..100.63 rows=1281 width=8) (actual  
time=3.937..4.031 rows=163 loops=1)
               Sort Key: t0.id_job_attribute
               ->  Index Scan using  
job_property__id_job_profile__fk_index on job_property t0   
(cost=0.00..31.31 rows=1281 width=8) (actual time=1.343..3.766 rows=163  
loops=1)
                     Index Cond: (id_job_profile = 911)
         ->  Sort  (cost=2451169.10..2483246.47 rows=12830947 width=8)  
(actual time=82891.076..-529619.213 rows=4187378 loops=1)
               Sort Key: t1.id_job_attribute
               ->  Hash IN Join  (cost=507.32..439065.37 rows=12830947  
width=8) (actual time=61.943..1874640.807 rows=4187378 loops=1)
                     Hash Cond: ("outer".id_job_profile =  
"inner".id_job_profile)
                     ->  Seq Scan on job_property t1   
(cost=0.00..246093.84 rows=12830947 width=12) (actual  
time=0.136..19101.796 rows=8482533 loops=1)
                           Filter: (id_job_profile <> 911)
                     ->  Hash  (cost=467.46..467.46 rows=15946 width=4)  
(actual time=61.313..61.313 rows=0 loops=1)
                           ->  Seq Scan on unemployed   
(cost=0.00..467.46 rows=15946 width=4) (actual time=0.157..50.842  
rows=15960 loops=1)
 Total runtime: 103769.592 ms



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend