Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

You can always store it divided in the database into two columns.
Gist could also work for you.

Thanks for the tip, unfortunately, split it does not work for me, since it’s a free text field, that users fill as they wish...

But looking most slow queries, this one pops up with more frequency...

So GIST would work; I’ll give a try.

Regards,

Edson.

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 10:01
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 always store it divided in the database into two columns.

Gist could also work for you.

Just discovered, I can’t use GIST over character varying...

Any other tips on how to optimize the query? Here are about 1,000,000 (one million) records in this table, table scan takes about 5 to 6 seconds on actual hardware (SAS, 1 Xeon, 2Gb memory on CentOS with all normal performance hacks).

select * from notafiscal where numeroctc like ‘POA%34345’;

Thanks,

Edson.

De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Em nome de Edson Carlos Ericksson Richter
Enviada em: segunda-feira, 26 de setembro de 2011 11:03
Para: pgsql-general@postgresql.org
Assunto: RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

Thanks for the tip, unfortunately, split it does not work for me, since it’s a free text field, that users fill as they wish...

But looking most slow queries, this one pops up with more frequency...

So GIST would work; I’ll give a try.

Regards,

Edson.

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 10:01
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 always store it divided in the database into two columns.

Gist could also work for you.

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.htmlfor
explanation).

Hope this helped.

2011/9/26 Edson Carlos Ericksson Richter <richter@simkorp.com.br>

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

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.

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&amp;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&amp;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 main distro 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 CygWin complains 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.

-----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.