BUG? Regular expression matching of optional character group at beginning of RE

From: Viktor Rosenfeld <listuser36(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: BUG? Regular expression matching of optional character group at beginning of RE
Date: 2012-07-06 18:49:51
Message-ID: 20120706184951.GA91000@client195-161.wlan.hu-berlin.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I've noticed that regular expressions which are anchored at the
beginning of the text but have an optional part at the beginning
(e.g. '^(ge)?kommen$') are not evaluated correctly if there is an
index on the column.

Consider the following table:

#+BEGIN_SRC sql
CREATE TABLE annotation (
id SERIAL PRIMARY KEY,
name VARCHAR(20),
value VARCHAR(20)
);
#+END_SRC

And the following query:

#+BEGIN_SRC sql
SELECT count(*) FROM annotation WHERE name = 'lemma' AND value ~ '^(ge)?kommen$';
#+END_SRC

In my data set, this query should return 916 results; 911 rows match
'kommen' and 5 rows match 'gekommen'.

Here is the plan with an index on column name:

#+BEGIN_EXAMPLE
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=20101.31..20101.32 rows=1 width=0)
-> Bitmap Heap Scan on annotation (cost=282.55..20101.31 rows=2 width=0)
Recheck Cond: ((name)::text = 'lemma'::text)
Filter: ((value)::text ~ '^(ge)?kommen$'::text)
-> Bitmap Index Scan on idx_test_name (cost=0.00..282.55 rows=15196 width=0)
Index Cond: ((name)::text = 'lemma'::text)
#+END_EXAMPLE

This plan correctly retrieves 916 rows. However, if I create an index
on the column value, the plan is as follows:

#+BEGIN_EXAMPLE
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=910.50..910.51 rows=1 width=0)
-> Bitmap Heap Scan on annotation (cost=619.38..910.49 rows=2 width=0)
Recheck Cond: ((name)::text = 'lemma'::text)
Filter: ((value)::text ~ '^(ge)?kommen$'::text)
-> BitmapAnd (cost=619.38..619.38 rows=76 width=0)
-> Bitmap Index Scan on idx_test_name (cost=0.00..282.55 rows=15196 width=0)
Index Cond: ((name)::text = 'lemma'::text)
-> Bitmap Index Scan on idx_test_value (cost=0.00..336.58 rows=15196 width=0)
Index Cond: (((value)::text ~>=~ 'ge'::text) AND ((value)::text ~<~ 'gf'::text))
#+END_EXAMPLE

This plan only retrieves the 5 rows matching 'gekommen'. Note the
usage of the index on the value column even though the first character
of the regular expression is not fixed.

The index creation command was:

#+BEGIN_SRC sql
CREATE INDEX idx_test_value ON annotation (value varchar_pattern_ops);
#+END_SRC

Note that I can force the correct evaluation of the regular expression
by prepending '(?e)'; however, this should not be necessary if I
understand the documentation correctly.

#+BEGIN_SRC sql
SELECT count(*) FROM annotation WHERE name = 'lemma' AND value ~ '(?e)^(ge)?kommen$';
#+END_SRC

I'm using PostgreSQL 9.1.4.

Cheers,
Viktor

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Viktor Rosenfeld 2012-07-06 18:53:53 Query runtime strongly dependent on generated statistics (and fewer statistics are better?)
Previous Message John R Pierce 2012-07-06 18:11:27 Re: index and data tablespaces on two separate drives or one RAID 0?