Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f34MlD811506; Wed, 4 Apr 2001 18:47:13 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f34Ml1w12882; Wed, 4 Apr 2001 18:47:01 -0400 (EDT) To: Phuong Ma cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] performance inconsistency In-reply-to: <3ACB9C4B.1071C2F1@commandprompt.com> References: <3ACB9C4B.1071C2F1@commandprompt.com> Comments: In-reply-to Phuong Ma message dated "Wed, 04 Apr 2001 15:12:27 -0700" Date: Wed, 04 Apr 2001 18:47:01 -0400 Message-ID: <12879.986424421@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200104/208 X-Sequence-Number: 7287 Phuong Ma writes: > WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.) > OR... > WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.) > However, the first query runs in about 10-15 seconds, and the second > query > ran for over 40 minutes before I cancelled it. What query plans does EXPLAIN show for each case? (And have you done VACUUM ANALYZE lately?) The change in the WHERE clause may be altering the planner's selectivity guesstimates enough to cause choice of a less appropriate plan. Hard to tell without seeing what EXPLAIN has to say, though. regards, tom lane