Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA55204 for ; Wed, 12 May 1999 18:57:29 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id SAA26091; Wed, 12 May 1999 18:56:18 -0400 (EDT) To: gjerde@icebox.org cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] JOIN index/sequential select problem In-reply-to: Your message of Wed, 12 May 1999 16:09:21 -0500 (CDT) Date: Wed, 12 May 1999 18:56:18 -0400 Message-ID: <26089.926549778@sss.pgh.pa.us> From: Tom Lane gjerde@icebox.org writes: > Why in the world is postgres selecting seq scan on the inventorysuppliers > table when doing an LIKE? That doesn't make sense to me. I'm guessing you might be compiling with LOCALE support turned on? The parser's hack to make LIKE comparisons indexable is only half functional in that case, since you get the >= comparison but not the <= one. Given the small size of your tables, the optimizer is probably estimating that an index scan isn't going to be selective enough to justify its extra cost. FWIW, I do get an index scan plan on an attempt to duplicate this case... but I'm not using LOCALE. We need to figure out a way to make LIKE indexable in non-ASCII locales. I think the best bet might be to try to generate a string "one greater" than the given initial string. In other words, given field LIKE 'ABC%' we want to transform to field LIKE 'ABC%' AND field >= 'ABC' AND field < 'ABD' so that the optimizer can use the last two clauses to constrain the index scan. But it's not real obvious how to generate a "larger" string in the general case with multibyte characters and non-straightforward collation order. Anyone have an idea how to do that? regards, tom lane