Skip site navigation (1) Skip section navigation (2)

Re: Text pattern JOINs that use indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Brooksby <rb(at)ravenbrook(dot)com>
Cc: joseph speigle <joe(dot)speigle(at)jklh(dot)us>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Text pattern JOINs that use indexes
Date: 2004-03-16 23:06:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Richard Brooksby <rb(at)ravenbrook(dot)com> writes:
> Well, you can write that, but it won't use a btree index on 
> bar_strings(string) because the planned doesn't know that the prefix 
> doesn't contain wildcards.  So instead we have to plan each lookup with 
> a constant string:

Another possibility is to manually hack up the query with the index
boundary conditions that the planner won't generate because it's not
sure about the wildcard situation.  Something like

	select * from prefixes, strings where
		string like (prefix || '%')
		and string >= prefix
		and string <= (prefix || '~~~~~~~');

The trick here is to generate the upper bound string correctly ---
I've cheated quite a lot in the above example by assuming that '~'
sorts larger than any character you'd actually have in your strings.
But if you know your data well you may be able to do this reliably.

Beware that the above will almost certainly not work if you're not
running in C locale.  Other locales have bizarre sorting rules that
will cause the >=/<= range to not match the prefix very well.  However,
if you are getting indexscan plans with plain constant patterns then
you are in C locale, because the planner can't solve that problem either...

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Tom AllisonDate: 2004-03-17 02:28:22
Subject: step 2..
Previous:From: Aarni RuuhimäkiDate: 2004-03-16 23:01:50
Subject: Re: Impact of UNICODE encoding on performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group