X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1B916D1B906 for ; Tue, 9 Mar 2004 15:37:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60122-09 for ; Tue, 9 Mar 2004 11:37:33 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id DC512D1BA44 for ; Tue, 9 Mar 2004 11:37:28 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i29FbUhu025392; Tue, 9 Mar 2004 10:37:30 -0500 (EST) To: David Teran Cc: Dennis Bjorklund , PgSQL Performance ML Subject: Re: speeding up a select with C function? In-reply-to: References: Comments: In-reply-to David Teran message dated "Tue, 09 Mar 2004 13:02:41 +0100" Date: Tue, 09 Mar 2004 10:37:30 -0500 Message-ID: <25391.1078846650@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/108 X-Sequence-Number: 5964 David Teran 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