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 3BB9FD1DADC for ; Sun, 7 Mar 2004 11:42:29 +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 39805-05 for ; Sun, 7 Mar 2004 07:42:29 -0400 (AST) Received: from ns2.rox.net (ns2.rox.net [212.63.65.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0090BD1D8A5 for ; Sun, 7 Mar 2004 07:42:26 -0400 (AST) Received: from localhost ([127.0.0.1]) by emma.rox.net with esmtp (Exim 4.22) id 1Azwft-0004aK-Gm for pgsql-performance@postgresql.org; Sun, 07 Mar 2004 12:42:29 +0100 Received: from [195.135.143.206] (helo=[10.0.1.2]) by emma.rox.net with asmtp (Exim 4.22) id 1Azwfs-0004ZZ-0K for pgsql-performance@postgresql.org; Sun, 07 Mar 2004 12:42:28 +0100 Mime-Version: 1.0 (Apple Message framework v612) Content-Transfer-Encoding: 7bit Message-Id: <7F2B9EA0-702C-11D8-9DA8-000A95C496AC@cluster9.com> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed To: PgSQL Performance ML From: David Teran Subject: speeding up a select with C function? Date: Sun, 7 Mar 2004 12:42:22 +0100 X-Mailer: Apple Mail (2.612) X-Scanned-By: rockenstein AG X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/86 X-Sequence-Number: 5942 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