Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f383Wj861359 for ; Sat, 7 Apr 2001 23:32:45 -0400 (EDT) (envelope-from pgsql-general-owner@postgresql.org) Received: from crazypenguins.commandprompt.com (commandprompt.com [64.81.28.146]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34J1T878693 for ; Wed, 4 Apr 2001 15:01:29 -0400 (EDT) (envelope-from poet@linuxports.com) Received: from crazypenguins.commandprompt.com (crazypenguins.commandprompt.com [64.81.28.146]) by crazypenguins.commandprompt.com (8.9.3/8.9.3) with ESMTP id LAA23488 for ; Wed, 4 Apr 2001 11:53:24 -0700 Date: Wed, 4 Apr 2001 11:53:24 -0700 (PDT) From: Poet/Joshua Drake X-Sender: To: Subject: [pgsql-general] Baffling Performance In-Reply-To: <20010404105738.A536@expressus.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200104/333 X-Sequence-Number: 7437 Good day, We're running a week-old CVS snapshot of PostgreSQL 7.1, and I'm not sure if this performance inconsistency is specific to it, or if this is just something in PostgreSQL in general, but it seems kind of odd, and I could use some help here. ;) I have run two queries in table full of invoices where the only different in the query is the where clause: SELECT COUNT(DISTINCT l."ORDER_NUM") AS line_orders, SUM(l."GROSS_PROFIT" + "REPLACEMENT_COST") AS grand_total, SUM(l."LIST_PRICE" * l."SHIPPED_QUANTITY") AS grand_total_alternative, SUM(l."GROSS_PROFIT") AS grand_total_profit FROM cu_invoice_li1_tbl l INNER JOIN cu_invoice_03_tbl h ON l."ORDER_NUM"=h."ORDER_NUM" WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.) OR... WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.) The first query returns more rows than the second (because it includes orders prefixed with I, C, F, etc), whereas the second only returns orders prefixed with I. It seems that the logic of identifying a single character as *not* being a single value would be identical or at least similar in efficiency to the logic of a single character *being* being a single value. However, the first query runs in about 10-15 seconds, and the second query ran for over 40 minutes before I cancelled it. Now, I can easily use a series of "!=" statements to get it down so that it's only the 'I' orders (which runs even faster, at about 5-6 seconds!), but can anyone explain this to me? :) Are "!=" substring evaluations inherently faster by an obscene order of magnitude, or is something really wrong here? J. -- -- CommandPrompt - http://www.commandprompt.com OpenDocs, LLC. - http://www.opendocs.org LinuxPorts - http://www.linuxports.com LDP - http://www.linuxdoc.org -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --