From: tjk@tksoft.com <tjk@tksoft.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] making 'like' queries quicker
Date: Sun, 19 Dec 1999 02:20:33 -0800 (PST)
Message-ID: <199912191020.CAA17780@uno.tksoft.com> (raw)
In-Reply-To: <1560.945556061@sss.pgh.pa.us>
Good point.
My attempt at a simplification oversimplified things a bit
where it shouldn't have.
Because records in the btree are based on an
alphabetic order, it is indeed possible to use an
index for finding records with 'abc%' etc.
Just like using a phonebook, really. You have
no problems using the index in a phonebook to
find your friend's number if you know the
first letters in your friend's last name. If
you don't know even just the first letter,
you will have to comb through the entire book
to find your friend.
When records are inserted into a btree, they are inserted
by comparing them to the other records in the table. This
means that they will always remain in a sorted order. The
backend can rearrange them to speed things up, but regardless,
you will always be able to find all values which are the same,
and all values which are lower or higher in value. In the case
of text strings, you would find records in an alpahabetical order.
Depending on the comparison function used, you will be able to
find records ordered according to different languages' alpahabet,
or another ordering mecchanism.
I haven't personally had the need to alter the comparison
function to sort strings, but you could do it by using a
different set of operators for the index. The default for
text columns is "text_ops," so you would change that to
your own set, or use one built-int, if one exists.
The docs contain info about this under "create index."
If you compiled with USE_LOCALE, it is possible that
sorting would be done according to the current locale,
but I can't say because I haven't tried it myself.
Troy
>
> "tjk@tksoft.com" <tjk@tksoft.com> writes:
> > A general rule of thumb is that indexes
> > only work on exact matches.
>
> Troy's rule of thumb is correct, but there's an important additional
> property of some types of indexes: you can scan them in order (for
> whatever kind of "order" is imposed by the index comparison operator).
> Postgres' btree indexes work that way, but hash indexes don't.
>
> An ordered index can be used to process inequalities and range
> queries as well as exact-match queries. For example, with a btree
> index you can do something like
> WHERE lastname >= 'Smith' AND lastname <= 'Szekely'
> fairly efficiently: you scan the portion of the index falling between
> the given limits, and then extract the main-table records pointed to
> by those index entries.
>
> Therefore, it's practical to use a btree index to speed up match queries
> that require a match at the start of the string. For example, given
> WHERE lastname LIKE 'Smith%'
> Postgres will generate additional clauses
> lastname >= 'Smith' AND lastname <= 'Smith\377'
> which can be used with a btree index to restrict the number of records
> that have to be looked at. You still have to do the LIKE comparison,
> in general (consider LIKE 'Smith%Jr') but you don't have to do it for
> every record in the table.
>
> There isn't any obvious way to apply this trick for an unanchored match,
> though (as in LIKE '%Smith%').
>
> However, if you are actually interested in searching for whole words,
> you could consider making an index that lists all of the whole words in
> your target field, and doing an exact match with that index. See
> contrib/fulltextindex in the Postgres distribution for an example.
>
> regards, tom lane
>
> ************
>
>
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: tjk@tksoft.com
Subject: Re: [SQL] making 'like' queries quicker
In-Reply-To: <199912191020.CAA17780@uno.tksoft.com>
References: <1560.945556061@sss.pgh.pa.us> <199912191020.CAA17780@uno.tksoft.com>
* 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