pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery planner: automatic rescribe of LIKE to BETWEEN ?
5+ messages / 4 participants
[nested] [flat]
* query planner: automatic rescribe of LIKE to BETWEEN ?
@ 2006-08-22 17:22 Ulrich Habel <espero7757@gmx.net>
0 siblings, 2 replies; 5+ messages in thread
From: Ulrich Habel @ 2006-08-22 17:22 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query planner: automatic rescribe of LIKE to BETWEEN ?
@ 2006-08-22 17:45 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ulrich Habel <espero7757@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2006-08-22 17:45 UTC (permalink / raw)
To: Ulrich Habel <espero7757@gmx.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query planner: automatic rescribe of LIKE to BETWEEN ?
@ 2006-08-22 18:21 Thomas Samson <koollman@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas Samson @ 2006-08-22 18:21 UTC (permalink / raw)
To: Ulrich Habel <espero7757@gmx.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query planner: automatic rescribe of LIKE to BETWEEN ?
@ 2006-08-22 18:49 Alvaro Herrera <alvherre@commandprompt.com>
parent: Thomas Samson <koollman@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2006-08-22 18:49 UTC (permalink / raw)
To: Thomas Samson <koollman@gmail.com>; +Cc: Ulrich Habel <espero7757@gmx.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query planner: automatic rescribe of LIKE to BETWEEN ?
@ 2006-08-22 21:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ulrich Habel <espero7757@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-08-22 21:12 UTC (permalink / raw)
To: espero7757@gmx.net; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-22 21:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-22 17:22 query planner: automatic rescribe of LIKE to BETWEEN ? Ulrich Habel <espero7757@gmx.net>
2006-08-22 17:45 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-22 18:21 ` Thomas Samson <koollman@gmail.com>
2006-08-22 18:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-22 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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