Thread: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

You can always store it divided in the database into two columns. 

You can create your own type, but that means writing bit code in C.


Please, stop the top posting!

On 26 September 2011 17:15, Edson Carlos Ericksson Richter
<richter@simkorp.com.br> wrote:
> Just discovered, I can’t use GIST over character varying...

Why do you think that?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Because the error message saying so (I do use pgAdmin III):

"An error has occurred:

ERROR: the datatype character varying has no standard operator class for
"gist" access method
HINT: You should specify na operator class for the index or define one
standard operator class for the data type."

(I've translated the above message from portuguese to english, sorry if it's
not exact).

That's why I can't use GIST.

Thanks,

Edson.


-----Mensagem original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Em nome de Alban Hertroys
Enviada em: segunda-feira, 26 de setembro de 2011 12:37
Para: Edson Carlos Ericksson Richter
Cc: pgsql-general@postgresql.org
Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
queries?

On 26 September 2011 17:15, Edson Carlos Ericksson Richter
<richter@simkorp.com.br> wrote:
> Just discovered, I can't use GIST over character varying...

Why do you think that?

--
If you can't see the forest for the trees, Cut the trees and you'll see
there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sorry, Gregg. I did not noticed I was disturbing...

Can you please tell me what you mean by "top posting"? I've created an
specific topic for this discussion, and I'm not using "urgent" or html
format... so I suppose that I've been following the rules...

BTW, I've repeated the query to easy people who would try to help me...
instead searching for the mail thread again (not everybody organizes mail list
in threads)...

Or should I not query for help on the issues I'm facing?


Regards,

Edson.


-----Mensagem original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Em nome de Gregg Jaskiewicz
Enviada em: segunda-feira, 26 de setembro de 2011 12:28
Para: Edson Carlos Ericksson Richter
Cc: pgsql-general@postgresql.org
Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
queries?

You can create your own type, but that means writing bit code in C.


Please, stop the top posting!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Edson,

1. PostgreSQL IS able to use btree index to execute this query.
More generally, it is able to use btree index for all PREFIX search.

2. You will need a special (NOT spatial) index for it
CREATE INDEX notafiscal_numeroctc_tpo_idx ON notafiscal (numeroctc text_pattern_ops);
( see http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html for explanation).


Hope this helped.

On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter
<richter@simkorp.com.br> wrote:
> select * from notafiscal where numeroctc like ‘POA%34345’;
>
> Prefix is normally 3 characters, suffix varyies.
>
> Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc to execute this query?

As mentioned by other posters, you should use a btree index with
text_pattern_ops opclass to speed up this query.

For queries like these, it's often faster to match the text in
*reverse*. You can create two indexes like this:

create index on foobar (txt text_pattern_ops);
create index on foobar (reverse(txt) text_pattern_ops);

And then write your queries like this:
SELECT * FROM foobar
WHERE txt like 'POA%34345'
  AND reverse(txt) like reverse('POA%34345');

PostgreSQL will automatically choose one or both indexes for executing
this query.

Regards,
Marti

> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] Em nome de Marti Raudsepp
> Enviada em: segunda-feira, 26 de setembro de 2011 17:42
> Para: Edson Carlos Ericksson Richter
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
> queries?
>
> On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter
> <richter@simkorp.com.br> wrote:
> > select * from notafiscal where numeroctc like ‘POA%34345’;
> >
> > Prefix is normally 3 characters, suffix varyies.
> >
> > Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc
> to execute this query?
>
> As mentioned by other posters, you should use a btree index with
> text_pattern_ops opclass to speed up this query.
>
> For queries like these, it's often faster to match the text in *reverse*.
> You can create two indexes like this:
>
> create index on foobar (txt text_pattern_ops); create index on foobar
> (reverse(txt) text_pattern_ops);

I got the following error:

ERROR: function reverse(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 29

>
> And then write your queries like this:
> SELECT * FROM foobar
> WHERE txt like 'POA%34345'
>   AND reverse(txt) like reverse('POA%34345');
>
> PostgreSQL will automatically choose one or both indexes for executing
> this query.
>
> Regards,
> Marti
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Tue, Sep 27, 2011 at 01:43, Edson Carlos Ericksson Richter
<richter@simkorp.com.br> wrote:
>> create index on foobar (txt text_pattern_ops); create index on foobar
>> (reverse(txt) text_pattern_ops);
>
> I got the following error:
>
> ERROR: function reverse(text) does not exist
> Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Ah, the reverse() function is not included with PostgreSQL 9.0 yet.
This is what I use:

CREATE FUNCTION reverse(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
  result text = '';
  i int;
BEGIN
  FOR i IN 1..length(input) BY 2 LOOP
    result = substr(input,i+1,1) || substr(input,i,1) || result;
  END LOOP;
  RETURN result;
END$$;

Regards,
Marti

In article <CABRT9RDxHKCxrq8wboHnikpF-CGgkteJWdw3Q2_kXFEdP4prTw@mail.gmail.com>,
Marti Raudsepp <marti@juffo.org> writes:

> Ah, the reverse() function is not included with PostgreSQL 9.0 yet.
> This is what I use:

> CREATE FUNCTION reverse(input text) RETURNS text
> LANGUAGE plpgsql IMMUTABLE STRICT AS $$
> DECLARE
>   result text = '';
>   i int;
> BEGIN
>   FOR i IN 1..length(input) BY 2 LOOP
>     result = substr(input,i+1,1) || substr(input,i,1) || result;
>   END LOOP;
>   RETURN result;
> END$$;

Pavel Stehule has found a better solution for that:

CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
  SELECT string_agg(substring($1 FROM i FOR 1), '')
  FROM generate_series(length($1), 1, -1) g(i)
$$ language sql;

But the best, of course, is upgrading to 9.1.

On Tue, Sep 27, 2011 at 13:00, Harald Fuchs <hari.fuchs@gmail.com> wrote:
> Pavel Stehule has found a better solution for that:
>
> CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
>  SELECT string_agg(substring($1 FROM i FOR 1), '')
>  FROM generate_series(length($1), 1, -1) g(i)
> $$ language sql;

I don't want to get into a pissing contest, but I'm not sure by which
criteria this is "better".

When I needed this function, I compared the speed many different
approaches (6 different versions from the mailing lists). The one I
posted above was the winner, a slightly tuned version of the original
by Shoaib Mir. When testing this right now, it takes half the time of
the function you posted, for short non-Unicode strings at least.

Regards,
Marti

> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] Em nome de Harald Fuchs
> Enviada em: terça-feira, 27 de setembro de 2011 07:01
> Para: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
> queries?
>
> In article <CABRT9RDxHKCxrq8wboHnikpF-
> CGgkteJWdw3Q2_kXFEdP4prTw@mail.gmail.com>,
> Marti Raudsepp <marti@juffo.org> writes:
>
> > Ah, the reverse() function is not included with PostgreSQL 9.0 yet.
> > This is what I use:
>
> > CREATE FUNCTION reverse(input text) RETURNS text LANGUAGE plpgsql
> > IMMUTABLE STRICT AS $$ DECLARE
> >   result text = '';
> >   i int;
> > BEGIN
> >   FOR i IN 1..length(input) BY 2 LOOP
> >     result = substr(input,i+1,1) || substr(input,i,1) || result;
> >   END LOOP;
> >   RETURN result;
> > END$$;
>
> Pavel Stehule has found a better solution for that:
>
> CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
>   SELECT string_agg(substring($1 FROM i FOR 1), '')
>   FROM generate_series(length($1), 1, -1) g(i) $$ language sql;
>
> But the best, of course, is upgrading to 9.1.

Upgrade to 9.1 into production servers is not na option.

It will take about a year before I can migrate all databases and establish
replication and everything else (probably, Christams Holidays in December or
Carnival in February next).

Some users demand 24x7 from this databases, I can't just say "stop for
one-two hour".

Thanks for the tip, I'll give a try for both funtions and let you know the
results. Would save time for future...


Regards,

Edson.

>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Alban, you forgot to reply to the mailing list. Please use the "reply
to all" button in your email client. :)

On Tue, Sep 27, 2011 at 14:21, Alban Hertroys <haramrae@gmail.com> wrote:
> If performance is an issue, I'd suggest coding it as a C function.
>
> A quick google search turned up:
> http://discuss.fogcreek.com/techInterview/default.asp?cmd=show&ixPost=2077
> for possible implementations.
>
> Of course, for Postgres we'd use pmalloc/pfree instead of malloc/free,
> as the result value will be a Datum.

These can't be adapted directly because they don't behave right in
UTF-8 encoding.

However, there's already a C implementation in the "orafce" project,
no point in reinventing the wheel:
http://pgfoundry.org/projects/orafce/

I have created a wiki page for collecting the most useful
implementations. Anyone is welcome to improve:
https://wiki.postgresql.org/wiki/Reverse_string

(I didn't include Pavel Stehule's implementation because that only
works on 9.0, not earlier. I did include another SQL implementation)

Regards,
Marti

> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] Em nome de Marti Raudsepp
> Enviada em: terça-feira, 27 de setembro de 2011 09:59
> Para: Alban Hertroys
> Cc: PG-General Mailing List
> Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
> queries?
>
> Alban, you forgot to reply to the mailing list. Please use the "reply to
> all" button in your email client. :)
>
> On Tue, Sep 27, 2011 at 14:21, Alban Hertroys <haramrae@gmail.com> wrote:
> > If performance is an issue, I'd suggest coding it as a C function.
> >
> > A quick google search turned up:
> > http://discuss.fogcreek.com/techInterview/default.asp?cmd=show&ixPost=
> > 2077
> > for possible implementations.
> >
> > Of course, for Postgres we'd use pmalloc/pfree instead of malloc/free,
> > as the result value will be a Datum.
>
> These can't be adapted directly because they don't behave right in
> UTF-8 encoding.
>
> However, there's already a C implementation in the "orafce" project, no
> point in reinventing the wheel:
> http://pgfoundry.org/projects/orafce/
>
> I have created a wiki page for collecting the most useful implementations.
> Anyone is welcome to improve:
> https://wiki.postgresql.org/wiki/Reverse_string

This wiki page is great! Best resource, for sure.

Would be nice to get precompiled binaries for orafce. It's very interesting package that IMHO should be included in
maindistro of Postgres. 

For Linux it's easy to get and compile... but for Win64 it's harder... good C compilers in Win64 is a nightmare (even
CygWincomplains about everything on the ".h world"). 

I'll try one of the portable solutions (for Win dev stations) and use the orafce in Linux srevers.

Thanks for all the tips!


Regards,

Edson.

>
> (I didn't include Pavel Stehule's implementation because that only works
> on 9.0, not earlier. I did include another SQL implementation)
>
> Regards,
> Marti
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

> -----Mensagem original-----
> De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] Em nome de Marti Raudsepp
> Enviada em: segunda-feira, 26 de setembro de 2011 17:42
> Para: Edson Carlos Ericksson Richter
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX
> queries?
>
> On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter
> <richter@simkorp.com.br> wrote:
> > select * from notafiscal where numeroctc like ‘POA%34345’;
> >
> > Prefix is normally 3 characters, suffix varyies.
> >
> > Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc
> to execute this query?
>
> As mentioned by other posters, you should use a btree index with
> text_pattern_ops opclass to speed up this query.
>
> For queries like these, it's often faster to match the text in *reverse*.
> You can create two indexes like this:
>
> create index on foobar (txt text_pattern_ops); create index on foobar
> (reverse(txt) text_pattern_ops);
>
> And then write your queries like this:
> SELECT * FROM foobar
> WHERE txt like 'POA%34345'
>   AND reverse(txt) like reverse('POA%34345');

Just perfect! It not only works, but time dropped from 5s to 94ms.

Regards,

Edson.

>
> PostgreSQL will automatically choose one or both indexes for executing
> this query.
>
> Regards,
> Marti
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general