Re: speeding up a select with C function?

David Teran <david(dot)teran(at)cluster9(dot)com> writes:
> 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.

Er, which part of that do you think is not using an index?

More generally, it is not necessarily the case that a join *should* use
an index. I'm a bit surprised that the above bothers to sort; I'd
expect a hash join to be more appropriate. Have you tried experimenting
with enable_mergejoin and the other planner-testing settings?

regards, tom lane