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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly