Skip site navigation (1) Skip section navigation (2)

Re: BUG #4899: Open parenthesis breaks query plan

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Cc: "Peter Headland" <pheadland(at)actuate(dot)com>
Subject: Re: BUG #4899: Open parenthesis breaks query plan
Date: 2009-07-05 10:25:26
Message-ID: 200907051225.27027.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-bugs
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
> 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

In response to

pgsql-bugs by date

Next:From: Steve PurcellDate: 2009-07-05 12:58:44
Subject: BUG #4901: Column name "window" breaks pg_dump/pg_restore
Previous:From: Oleg SerovDate: 2009-07-05 09:28:25
Subject: Re: Diffrent column ordering after dump/restore tables with INHERITS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group