X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7D3CFD1B34F for ; Tue, 13 Jul 2004 13:13:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22874-03 for ; Tue, 13 Jul 2004 16:13:48 +0000 (GMT) Received: from web51404.mail.yahoo.com (web51404.mail.yahoo.com [206.190.38.183]) by svr1.postgresql.org (Postfix) with SMTP id D5D3ED1B170 for ; Tue, 13 Jul 2004 13:13:46 -0300 (ADT) Message-ID: <20040713161346.62000.qmail@web51404.mail.yahoo.com> Received: from [192.88.67.254] by web51404.mail.yahoo.com via HTTP; Tue, 13 Jul 2004 09:13:46 PDT Date: Tue, 13 Jul 2004 09:13:46 -0700 (PDT) From: Bill Chandler Subject: Re: [PERFORM] Cursors performance To: Kris Jurka Cc: pgsql-jdbc@postgresql.org, pgsql-perform@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200407/129 X-Sequence-Number: 10513 All, Looks like I may have beaten this one to death. May have to chalk it up to a limitation for now due to deadlines and revisit it later. One final clue before I go: if I change my wildcard to 'NEPOOL%' from 'NEPOOL%REAL%' my query completes much faster. Of course this makes sense since it's much easier to search a string for a prefix than it is to do a complex regular expression match. I just didn't expect it to be orders of magnitude difference. The table containing the string being searched is only 7500 rows but I am joining it with a table with 2.5 million rows. So maybe there's something I can do to do the wildcard search on the smaller table first then do the join. Ok, thanks again to all who responded. Really appreciate the tips on logging statements and duration, etc. regards, Bill --- Kris Jurka wrote: > > > On Mon, 12 Jul 2004, Barry Lind wrote: > > > Bill, > > > > I suspect that this is an artifact of using server > side prepared > > statements. When testing this via psql you will > be forming sql like: > > I don't think so. The 7.4 driver can use either > cursors or server > prepared statements, not both. He's definitely > using cursors, so I server > prepared statements don't come into the mix here. > > Kris Jurka > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail