Thread: query planner: automatic rescribe of LIKE to BETWEEN ?

Hello all,
had an idea of optimizing a query that may work generally.

In case a 'column' is indexed, following two alterations could be done
I think:

A)

  select ... where column ~ '^Foo'     -->  Seq Scan

into that:

  select ... where column BETWEEN 'Foo' AND 'FooZ'     -->  Index Scan

of course 'Z' should be the last possible character internally used of the
DBMS.

That would work as long as there is no in-case-sensitive search being done.


another rescribtion:

B)

  select ... where  column ~ '^Foo$'     -->  Seq Scan

into that:

  select ... where  column =  'Foo'     -->  Bitmap Heap Scan

That speeds up things, too.



That would also apply to 'LIKE' and 'SIMILAR TO' operations, I think.

Is there any idea to make the "Query Planner" more intelligent to do these
convertions automatically?

Anythings speeks against this hack?

Regards
  Uli Habel

Ulrich Habel wrote:
> Hello all,
> had an idea of optimizing a query that may work generally.
>
> In case a 'column' is indexed, following two alterations could be done
> I think:
>
> A)
>
>   select ... where column ~ '^Foo'     -->  Seq Scan

This is not true.  You can make this query use an index if you create it
with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.

Thus you don't need any hack here.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 8/22/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Ulrich Habel wrote:
> > Hello all,
> > had an idea of optimizing a query that may work generally.
> >
> > In case a 'column' is indexed, following two alterations could be done
> > I think:
> >
> > A)
> >
> >   select ... where column ~ '^Foo'     -->  Seq Scan
>
> This is not true.  You can make this query use an index if you create it
> with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.
>
> Thus you don't need any hack here.
>

And in the case of more general expression, like:
 select ... where column ~ 'something';

Is there a way to optimise this ? (in the case where 'something' is not
a word, but a part of a word)

--
Thomas SAMSON

Thomas Samson wrote:
> On 8/22/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >Ulrich Habel wrote:
> >> Hello all,
> >> had an idea of optimizing a query that may work generally.
> >>
> >> In case a 'column' is indexed, following two alterations could be done
> >> I think:
> >>
> >> A)
> >>
> >>   select ... where column ~ '^Foo'     -->  Seq Scan
> >
> >This is not true.  You can make this query use an index if you create it
> >with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.
> >
> >Thus you don't need any hack here.
> >
>
> And in the case of more general expression, like:
> select ... where column ~ 'something';
>
> Is there a way to optimise this ? (in the case where 'something' is not
> a word, but a part of a word)

Not sure.  I'd try tsearch2 or pg_trgm (or pg_tgrm, whatever it's
called).  It's trigram indexing.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Ulrich Habel <espero7757@gmx.net> writes:
> Anythings speeks against this hack?

Only that it was done years ago.

As Alvaro mentions, if you are using a non-C locale then you need
non-default index opclasses to get it to work.  Non-C locales usually
have index sort orders that don't play nice with this conversion.

            regards, tom lane