The following bug has been logged online:
Bug reference: 4899
Logged by: Peter Headland
Email address: pheadland@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.