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(at)actuate(dot)com
> PostgreSQL version: 8.4.0
> Operating system: Windows
> Description: Open parenthesis breaks query plan
> 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
Could you provide 'EXPLAIN ANALYZE' output for both queries?
To make sure its not a bug directly caused by the parentheses you can do
EXPLAIN ANALYZE yourquery_with_paren;
in the same connection and check whether this uses an index.
In response to
pgsql-bugs by date
|Next:||From: Steve Purcell||Date: 2009-07-05 12:58:44|
|Subject: BUG #4901: Column name "window" breaks pg_dump/pg_restore|
|Previous:||From: Oleg Serov||Date: 2009-07-05 09:28:25|
|Subject: Re: Diffrent column ordering after dump/restore tables with INHERITS|