Обсуждение: Change in 9.1?

<p>På tirsdag 22. november 2011 kl 08:39:09 skrev <strong>Jasmin Dizdarevic</strong> <<a
href="mailto:jasmin.dizdarevic@gmail.com">jasmin.dizdarevic@gmail.com</a>>:<blockquotestyle="border-left: 1px solid
rgb(204,204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi,  <div> </div><div>we have a reporting tool, that
sometimesuses this kind of condition.</div><div>...WHERE a.field = a.field </div><div> </div><div>To explain this:
a.fieldcan be filtered by the user. the user can choose some values. if he does, this condition will be
build:</div><div>...WHEREa.field IN (1,2,3)</div><div> </div><div>If the user doesn't choose any values the * = *
conditionis used.</div><div> </div><div>Since 9.1 we're experiencing problems with this construction. Have there been
anychanges to the planner regarding this?</div></blockquote><p> <p>It would be far easier to give you an answer if you
provideda query which worked before which now, in 9.1, gives you trouble.<br /><br /><span style="font-family:
monospace;font-size: 10px;">--<br /> Andreas Joseph Krogh <andreak@officenet.no> - mob: +47 909 56 963<br />
SeniorSoftware Developer / CTO - OfficeNet AS - http://www.officenet.no<br /> Public key:
http://home.officenet.no/~andreak/public_key.asc</span><p> 

On 11/22/2011 12:39 AM, Jasmin Dizdarevic wrote:
> Hi, 
>
> we have a reporting tool, that sometimes uses this kind of condition.
> ...WHERE a.field = a.field 
>
> To explain this: a.field can be filtered by the user. the user can
> choose some values. if he does, this condition will be build:
> ...WHERE a.field IN (1,2,3)
>
> If the user doesn't choose any values the * = * condition is used.
>
> Since 9.1 we're experiencing problems with this construction. Have
> there been any changes to the planner regarding this?
>
> Ty
> Regards, Jasmin

If it's a commercial product please name that reporting tool: clearly
it's to be avoided.  If it's an in-house tool clearly it's broken.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Rob Sargent
Sent: Tuesday, November 22, 2011 10:30 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Change in 9.1?



On 11/22/2011 12:39 AM, Jasmin Dizdarevic wrote:
> Hi,
>
> we have a reporting tool, that sometimes uses this kind of condition.
> ...WHERE a.field = a.field
>
> To explain this: a.field can be filtered by the user. the user can 
> choose some values. if he does, this condition will be build:
> ...WHERE a.field IN (1,2,3)
>
> If the user doesn't choose any values the * = * condition is used.
>
> Since 9.1 we're experiencing problems with this construction. Have 
> there been any changes to the planner regarding this?
>
> Ty
> Regards, Jasmin

If it's a commercial product please name that reporting tool: clearly it's
to be avoided.  If it's an in-house tool clearly it's broken.

------------------------------------------------------------

While probably not optimal Dynamic SQL isn't the easiest thing to construct
and so having the occasional sub-optimal construct shouldn't be taken as
being wrong - it should at least result in a reasonably optimal, and
correct, query plan.

The bigger problem is that the OP has utterly failed to describe what kinds
of "problems" are being experienced AND has not provided enough data to
properly evaluate the situation.  Providing a self-contained test case would
at least allow people to run the query and see what is happening...and at a
minimum a query plan (with analyze ideally) - from both versions - would at
least allow some degree of analysis even without a full test-case. 

Since the data matters proving all of: a test case, AND EXPLAIN ANALYZE
results, AND a description of what is taken as being a problem, is necessary
to provide suggestions.

David J.