pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: gjerde@icebox.org
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] JOIN index/sequential select problem
Date: Wed, 12 May 1999 18:56:18 -0400
Message-ID: <26089.926549778@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.05.9905121559230.30984-100000@snowman.icebox.org>

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



view thread (10+ messages)  latest in thread

Message-ID: <26089.926549778@sss.pgh.pa.us>
Permalink:  ../26089.926549778@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26089.926549778@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, gjerde@icebox.org
  Subject: Re: [SQL] JOIN index/sequential select problem
  In-Reply-To: <26089.926549778@sss.pgh.pa.us>
  References: <Pine.LNX.4.05.9905121559230.30984-100000@snowman.icebox.org> <26089.926549778@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox