While noodling around some more, I found that a comparison to '()' allows use
of the index, as does '(abc)' and even '(a(b(c)d)e)'. It appears that
mismatched open/close paren pairs trigger the bug. Obviously something is
parsing the string literal and mishandling parentheses. I don't understand why
parentheses should be significant inside a string literal in the first place.
Also, just to be 100% clear, the open paren can be anywhere in the string, so a
comparison to 'abcdefgh(ijklmnop' still triggers the bug.
--
Peter Headland
Architect - e.Reports
Actuate Corporation
-----Original Message-----
From: Peter Headland
Sent: Saturday, July 04, 2009 18:03
To: pgsql-bugs@postgresql.org
Subject: BUG #4899: Open parenthesis breaks query plan
The following bug has been logged online:
Bug reference: 4899
Logged by: Peter Headland
Email address: pheadl...@actuate.com
PostgreSQL version: 8.4.0
Operating system: Windows
Description: Open parenthesis breaks query plan
Details:
In a moderate-size table (~400,000 rows), an equality match on an unindexed
varchar column to a string that contains an open parenthesis '(' prevents
the optimizer from using an obvious index. Changing the open parenthesis to
another character, such as ')' allows the obvious index to be used. I have
been unable to reproduce this on simple test data so far, so it is obviously
fairly subtle.
Abstract example of the issue:
o table t has a composite index i comprising columns c1, c2, c3
o column t.c4 is not indexed
Illustration of the queries:
-- Full table scan
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = '(';
-- Uses index i
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = ')';
I am really hoping that this defect can be found by inspection of the
source, because trying to reproduce it is fast getting me nowhere.
Unfortunately, the data involved are customer confidential, so I cannot
provide the original table.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
