Thread: What is impact of "varchar_opts"?

I'm wondering why "varchar_opts" is not default operator class for all
indexed varchar field.
Is the impact to heavy?

Thanks for the clarification,

Edson

Edson Richter <edsonrichter@hotmail.com> writes:
> I'm wondering why "varchar_opts" is not default operator class for all
> indexed varchar field.

varchar has no operators of its own; it just relies on the operators for
type text.  Therefore text_ops is the formally correct choice.  The
varchar_ops opclass is just an alias that's there so we don't get bug
reports from pedants who expect varchar to have a varchar_ops opclass.

            regards, tom lane

Em 21/01/2013 17:18, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> writes:
>> I'm wondering why "varchar_opts" is not default operator class for all
>> indexed varchar field.
> varchar has no operators of its own; it just relies on the operators for
> type text.  Therefore text_ops is the formally correct choice.  The
> varchar_ops opclass is just an alias that's there so we don't get bug
> reports from pedants who expect varchar to have a varchar_ops opclass.
>
>             regards, tom lane
>
>
I see. So, what is the overhead of having text_ops in opclass?
Can I define it as default for all my indexes when textual type of any kind?

Thanks,

Edson

Edson Richter <edsonrichter@hotmail.com> writes:
> I see. So, what is the overhead of having text_ops in opclass?
> Can I define it as default for all my indexes when textual type of any kind?

Why are you intent on defining anything?  IMO, best practice is to let
the database choose the opclass, unless you have a very good and
specific reason to choose a non-default one for a particular index.
Letting it default is way more future-proof than specifying something.

            regards, tom lane

Em 21/01/2013 18:03, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> writes:
>> I see. So, what is the overhead of having text_ops in opclass?
>> Can I define it as default for all my indexes when textual type of any kind?
> Why are you intent on defining anything?  IMO, best practice is to let
> the database choose the opclass, unless you have a very good and
> specific reason to choose a non-default one for a particular index.
> Letting it default is way more future-proof than specifying something.
>
>             regards, tom lane
>
>

Thanks, but I've found that some queries using LIKE operator uses table
scan instead index unless it is defined with varchar_ops in the index...

That make a huge difference when querying tables with millions of
objects (indexed vs table scan). And I can't avoid the LIKE operator...

Example:

select * from notafiscal where cnpj like '01234568%'

Is there other way I'm missing?

Thanks,

Edson

Edson Richter <edsonrichter@hotmail.com> writes:
> Thanks, but I've found that some queries using LIKE operator uses table
> scan instead index unless it is defined with varchar_ops in the index...

You mean varchar_pattern_ops?  That's an entirely different animal.

            regards, tom lane

Em 21/01/2013 18:36, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> writes:
>> Thanks, but I've found that some queries using LIKE operator uses table
>> scan instead index unless it is defined with varchar_ops in the index...
> You mean varchar_pattern_ops?  That's an entirely different animal.
>
>             regards, tom lane
>

Yeah, sure. The animal is me ;-) (just kidding, no offense!). I
expressed myself in wrong terminology, and wasted time. Sorry.

I mean varchar_pattern_ops. Most of my indexes with varchar fields need
to be created with this option to improve search with like operator.

Is there any way to specify this is the default option? What would be
the overall impact over the database?

Thanks,

Edson

I'm rephrasing the question I posted yesterday, because I have used the
wrong terminology and caused confusion (and for instance, got no
response to my question).

Almost all indexed columns of kind varchar in my database require
"varchar_pattern_ops" op class in order to make my LIKE queries more
optmized.

Is there any way to define that this operator class is the default for
my database for varchar columns?

What would be the impact in terms of performance and memory consumption?


Thanks,

Edson Richter

Em 22/01/2013 15:57, Edson Richter escreveu:
> I'm rephrasing the question I posted yesterday, because I have used
> the wrong terminology and caused confusion (and for instance, got no
> response to my question).
>
> Almost all indexed columns of kind varchar in my database require
> "varchar_pattern_ops" op class in order to make my LIKE queries more
> optmized.
>
> Is there any way to define that this operator class is the default for
> my database for varchar columns?
>
> What would be the impact in terms of performance and memory consumption?
>
>
> Thanks,
>
> Edson Richter
>
>
Found it (partial) answer!

RTFM, as the wise man said. If you have not found, then have you read
not enough.

If anyone else need to do the same, look the manuals...
http://www.postgresql.org/docs/current/static/sql-alteropclass.html

BUT, I have not found how to change the existing operator class to be
default for the data type without dropping and recreating.

It is there, or are the docs outdated?

Thanks,

Edson Richter

Edson Richter <edsonrichter@hotmail.com> writes:
> Almost all indexed columns of kind varchar in my database require
> "varchar_pattern_ops" op class in order to make my LIKE queries more
> optmized.

> Is there any way to define that this operator class is the default for
> my database for varchar columns?

No.  (Well, you could go and hack the catalog entries to mark it as the
default, but things would fail rather badly whenever you dump and reload
the database, because pg_dump won't dump changes to built-in objects.)

> What would be the impact in terms of performance and memory consumption?

It's probably cheaper, actually, than the regular strcoll-based
comparisons.

BTW, have you considered whether you could run your database in C locale
and thus dodge the whole problem?  In C locale there's no difference
between this opclass and text_ops.

            regards, tom lane

Em 22/01/2013 18:04, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> writes:
>> Almost all indexed columns of kind varchar in my database require
>> "varchar_pattern_ops" op class in order to make my LIKE queries more
>> optmized.
>> Is there any way to define that this operator class is the default for
>> my database for varchar columns?
> No.  (Well, you could go and hack the catalog entries to mark it as the
> default, but things would fail rather badly whenever you dump and reload
> the database, because pg_dump won't dump changes to built-in objects.)

Can I change this in template database, and then get the benefit of
having these changes propagated to newly created databases?

>
>> What would be the impact in terms of performance and memory consumption?
> It's probably cheaper, actually, than the regular strcoll-based
> comparisons.
>
> BTW, have you considered whether you could run your database in C locale
> and thus dodge the whole problem?  In C locale there's no difference
> between this opclass and text_ops.

Using C locale, would I face problems with Portuguese Brazilian
characters like ã, ç, é?

Thanks, Tom!


Edson

>
>             regards, tom lane
>
>