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