Re: BUG #4899: Open parenthesis breaks query plan

On Sunday 05 July 2009 03:03:00 Peter Headland wrote:
> 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.
I think this is not caused by a bug but, maybe wrong, selectivity estimates.
I.e. in one case the planner thinks your query will match a small enough
portion of the query, so that an index will be usefull , in the other case
not.
Could you provide 'EXPLAIN ANALYZE' output for both queries?

To make sure its not a bug directly caused by the parentheses you can do
    SET enable_seqscan=off;
    EXPLAIN ANALYZE yourquery_with_paren;
in the same connection and check whether this uses an index.

Andres



Andres