In order to get like queries to use an index with database initialized
with a UTF-8 character set I added a unique index to a table with a
varchar_pattern_ops

This table already had a unique constraint on the column so I dropped
the unique constraint.

I can't give exact measurements however this caused my application to
slow down considerably.

The only thing I can figure is that the varchar_pattern_ops operator
is significantly slower ???

Is there some other piece of the puzzle to fill in ?

Dave

Search Discussions

  • Alvaro Herrera at Nov 12, 2007 at 2:59 pm

    Dave Cramer wrote:
    In order to get like queries to use an index with database initialized with
    a UTF-8 character set I added a unique index to a table with a
    varchar_pattern_ops

    This table already had a unique constraint on the column so I dropped the
    unique constraint.

    I can't give exact measurements however this caused my application to slow
    down considerably.

    The only thing I can figure is that the varchar_pattern_ops operator is
    significantly slower ???

    Is there some other piece of the puzzle to fill in ?
    Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries,
    whereas the other one is going to be used for = queries. So you need to
    keep both indexes.

    --
    Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
    Officer Krupke, what are we to do?
    Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")
  • Dave Cramer at Nov 12, 2007 at 3:13 pm

    On 12-Nov-07, at 9:56 AM, Alvaro Herrera wrote:

    Dave Cramer wrote:
    In order to get like queries to use an index with database
    initialized with
    a UTF-8 character set I added a unique index to a table with a
    varchar_pattern_ops

    This table already had a unique constraint on the column so I
    dropped the
    unique constraint.

    I can't give exact measurements however this caused my application
    to slow
    down considerably.

    The only thing I can figure is that the varchar_pattern_ops
    operator is
    significantly slower ???

    Is there some other piece of the puzzle to fill in ?
    Well, AFAIK the index with varchar_pattern_ops is used for LIKE
    queries,
    whereas the other one is going to be used for = queries. So you
    need to
    keep both indexes.
    You would be correct, thanks for the quick answer.

    Dave
  • Tom Lane at Nov 12, 2007 at 4:38 pm

    Alvaro Herrera writes:
    Well, AFAIK the index with varchar_pattern_ops is used for LIKE queries,
    whereas the other one is going to be used for = queries. So you need to
    keep both indexes.
    Given the current definition of text equality, it'd be possible to drop
    ~=~ and have the standard = operator holding the place of equality in
    both the regular and pattern_ops opclasses. Then it'd be possible to
    support regular equality queries, as well as LIKE, with only the
    pattern_ops index.

    This would break any applications explicitly using ~=~, but how many
    of those are there?

    (For backwards compatibility it'd be nice if we could allow both = and
    ~=~ in the opclass, but the unique index on pg_amop seems to preclude
    that.)

    regards, tom lane
  • Dave Cramer at Nov 12, 2007 at 6:32 pm

    On 12-Nov-07, at 11:37 AM, Tom Lane wrote:

    Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
    Well, AFAIK the index with varchar_pattern_ops is used for LIKE
    queries,
    whereas the other one is going to be used for = queries. So you
    need to
    keep both indexes.
    Given the current definition of text equality, it'd be possible to
    drop
    ~=~ and have the standard = operator holding the place of equality in
    both the regular and pattern_ops opclasses. Then it'd be possible to
    support regular equality queries, as well as LIKE, with only the
    pattern_ops index.
    That would be ideal. Having two indexes on the same column isn't
    optimal.

    Dave

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedNov 12, '07 at 2:51p
activeNov 12, '07 at 6:32p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase