X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 50B0AD1C9DB for ; Tue, 5 Aug 2003 17:45:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 25968-05 for ; Tue, 5 Aug 2003 14:44:12 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 34219D1C4E0 for ; Tue, 5 Aug 2003 14:45:20 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.6) with ESMTP-TLS id 23712; Tue, 05 Aug 2003 10:06:32 -0700 Message-ID: <3F2FEC57.5010201@joeconway.com> Date: Tue, 05 Aug 2003 10:41:43 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Medora Schauer Cc: postgresql Subject: Re: Odd performance results - more info References: <906E2C446A276048A1BE283F17BCB12CB137CA@encounter.fairind.fairfield.com> In-Reply-To: <906E2C446A276048A1BE283F17BCB12CB137CA@encounter.fairind.fairfield.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/80 X-Sequence-Number: 2897 Medora Schauer wrote: > I would greatly appreciate it if someone could run this code in their > environment and let me know if you get results similiar to mine. > The INT test results in execution times of 11 - 50+ secs increasing > each time the test is run. The FLOAT test execution times are > consistently < 3 secs regardless of how many times it is run. Without actually trying the code, I'd bet that an index is getting used for the float8 case, but not in the int8 case: if (dtype == INT_TYPE){ snprintf(buffer, sizeof(buffer), "UPDATE shot_record SET trace_count = %d " \ "WHERE shot_line_num = %d " \ " AND shotpoint = %d " \ " AND index = %d" , 0, shotline, shotpoint + i, 0); } else { snprintf(buffer, sizeof(buffer), "UPDATE shot_record SET trace_count = %d " \ "WHERE shot_line_num = %f " \ " AND shotpoint = %f " \ " AND index = %d" , 0, (float)shotline, (float)shotpoint + (float)i, 0); } Try running EXPLAIN ANALYZE on these update statements manually. It also might help to run VACUUM ANALYZE after populating the tables. HTH, Joe