Hi Dennis,
>> we need to optimize / speed up a simple select:
>>
>> explain analyze select
>> ((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value))
>> from job_property t0, job_property t1
>> where t0.id_job_profile = 5
>> and t1.id_job_profile = 6
>> and t1.id_job_attribute = t0.id_job_attribute
>> and t1.int_value < t0.int_value;
>
> Try to add an index on (id_job_profile, id_job_attribute) or maybe even
> (id_job_profile, id_job_attribute, int_value)
>
Tried this but the index is not used. I know the same problem was true
with a FrontBase database so i wonder how i can force that the index is
used. As i was not sure in which order the query is executed i decided
to create indexes for all variations:
id_job_profile, id_job_attribute, int_value
id_job_profile, int_value, id_job_attribute
int_value, id_job_attribute, id_job_profile,
int_value, id_job_profile, id_job_attribute
....
here is the output:
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------
Merge Join (cost=5369.08..5383.14 rows=150 width=4) (actual
time=2.527..2.874 rows=43 loops=1)
Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
Join Filter: ("inner".int_value < "outer".int_value)
-> Sort (cost=2684.54..2686.37 rows=734 width=6) (actual
time=1.140..1.177 rows=232 loops=1)
Sort Key: t0.id_job_attribute
-> Index Scan using
job_property_short__id_job_profile__fk_index on job_property_short t0
(cost=0.00..2649.60 rows=734 width=6) (actual time=0.039..0.820
rows=232 loops=1)
Index Cond: (id_job_profile = 5)
-> Sort (cost=2684.54..2686.37 rows=734 width=6) (actual
time=1.175..1.223 rows=254 loops=1)
Sort Key: t1.id_job_attribute
-> Index Scan using
job_property_short__id_job_profile__fk_index on job_property_short t1
(cost=0.00..2649.60 rows=734 width=6) (actual time=0.023..0.878
rows=254 loops=1)
Index Cond: (id_job_profile = 6)
Total runtime: 3.065 ms
(12 rows)
So the question is how to tell Postgres to use the index.
regards David
