Hi,

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;

the result from explain analyze is:

first run:
------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------
Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual time=226.544..226.890 rows=43 loops=1)
Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
Join Filter: ("inner".int_value < "outer".int_value)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=113.781..113.826 rows=232 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..4105.87 rows=1026 width=8) (actual time=0.045..113.244 rows=232 loops=1)
Index Cond: (id_job_profile = 5)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=112.504..112.544 rows=254 loops=1)
Sort Key: t1.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.067..112.090 rows=254 loops=1)
Index Cond: (id_job_profile = 6)
Total runtime: 227.120 ms
(12 rows)


second run:
------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------------
Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual time=4.323..4.686 rows=43 loops=1)
Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
Join Filter: ("inner".int_value < "outer".int_value)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=2.666..2.700 rows=232 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..4105.87 rows=1026 width=8) (actual time=0.279..2.354 rows=232 loops=1)
Index Cond: (id_job_profile = 5)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=1.440..1.477 rows=254 loops=1)
Sort Key: t1.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.040..1.133 rows=254 loops=1)
Index Cond: (id_job_profile = 6)
Total runtime: 4.892 ms
(12 rows)



I have run vacuum analyze before executing the statements. I wonder now if there is any chance to speed this up. Could we use a C function to access the indexes faster or is there any other chance to speed this up?


The Server is a dual G5/2GHZ with 8 GB of RAM and a 3.5 TB fiberchannel RAID. The job_property table is about 1 GB large (checked with dbsize) and has about 6.800.000 rows.


regards David



---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to