speeding up a select with C function? - Mailing list pgsql-performance

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