pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChange in 9.1?
3+ messages / 3 participants
[nested] [flat]
* Change in 9.1?
@ 2011-11-22 07:39 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jasmin Dizdarevic @ 2011-11-22 07:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Change in 9.1?
@ 2011-11-22 11:54 Andreas Joseph Krogh <andreak@officenet.no>
parent: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas Joseph Krogh @ 2011-11-22 11:54 UTC (permalink / raw)
To: pgsql-sql
<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>>:</p>
<blockquote style="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 sometimes uses this kind of condition.</div>
<div>...WHERE a.field = a.field </div>
<div> </div>
<div>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:</div>
<div>...WHERE a.field IN (1,2,3)</div>
<div> </div>
<div>If the user doesn't choose any values the * = * condition is used.</div>
<div> </div>
<div>Since 9.1 we're experiencing problems with this construction. Have there been any changes to the planner regarding this?</div>
</blockquote>
<p> </p>
<p>It would be far easier to give you an answer if you provided a 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 />
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no<br />
Public key: http://home.officenet.no/~andreak/public_key.asc</span></p;
<p> </p>
From pgsql-sql-owner@postgresql.org Tue Nov 22 11:30:06 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
by mail.postgresql.org (Postfix) with ESMTP id 4F6201ED23D7
for <pgsql-sql@postgresql.org>; Tue, 22 Nov 2011 11:30:04 -0400 (AST)
Received: from out02.mta.xmission.com ([166.70.13.232])
by magus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <robjsargent@gmail.com>)
id 1RSsIN-0006uX-4u
for pgsql-sql@postgresql.org; Tue, 22 Nov 2011 15:30:04 +0000
Received: from in02.mta.xmission.com ([166.70.13.52])
by out02.mta.xmission.com with esmtps (TLSv1:AES256-SHA:256)
(Exim 4.69)
(envelope-from <robjsargent@gmail.com>)
id 1RSsI9-0003kh-C3; Tue, 22 Nov 2011 08:29:49 -0700
Received: from [208.187.158.94] (helo=[172.16.1.26])
by in02.mta.xmission.com with esmtpsa (TLSv1:AES256-SHA:256)
(Exim 4.69)
(envelope-from <robjsargent@gmail.com>)
id 1RSsI9-0003n0-0C; Tue, 22 Nov 2011 08:29:49 -0700
Message-ID: <4ECBBFEA.4080005@gmail.com>
Date: Tue, 22 Nov 2011 08:29:46 -0700
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.24) Gecko/20111101 SUSE/3.1.16 Lightning/1.0b2 Thunderbird/3.1.16
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
References: <CAOveQuPRqy0NoJkNFMRAhkJoWoc8jHSMz8fKdf56Xjzz5Gw27A@mail.gmail.com>
In-Reply-To: <CAOveQuPRqy0NoJkNFMRAhkJoWoc8jHSMz8fKdf56Xjzz5Gw27A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-XM-SPF: eid=;;;mid=;;;hst=in02.mta.xmission.com;;;ip=208.187.158.94;;;frm=robjsargent@gmail.com;;;spf=neutral
X-XM-AID: U2FsdGVkX1/+Tv6z0cwWjrHYirpICGiYIUKQVtNVexE=
X-SA-Exim-Connect-IP: 208.187.158.94
X-SA-Exim-Mail-From: robjsargent@gmail.com
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on sa07.xmission.com
X-Spam-Level:
X-Spam-Status: No, score=-0.4 required=8.0 tests=ALL_TRUSTED,BAYES_20,
DCC_CHECK_NEGATIVE,DNS_FROM_RFC_ABUSE,FREEMAIL_FROM,T_TM2_M_HEADER_IN_MSG,
UNTRUSTED_Relay autolearn=disabled version=3.3.1
X-Spam-Report:
* -1.0 ALL_TRUSTED Passed through trusted hosts only via SMTP
* 0.0 FREEMAIL_FROM Sender email is commonly abused enduser mail provider
* (robjsargent[at]gmail.com)
* 0.0 T_TM2_M_HEADER_IN_MSG BODY: T_TM2_M_HEADER_IN_MSG
* -0.0 BAYES_20 BODY: Bayes spam probability is 5 to 20%
* [score: 0.1277]
* -0.0 DCC_CHECK_NEGATIVE Not listed in DCC
* [sa07 1397; Body=1 Fuz1=1 Fuz2=1]
* 0.2 DNS_FROM_RFC_ABUSE Envelope sender in abuse.rfc-ignorant.org
* 0.4 UNTRUSTED_Relay Comes from a non-trusted relay
X-Spam-DCC: XMission; sa07 1397; Body=1 Fuz1=1 Fuz2=1
X-Spam-Combo: ;pgsql-sql@postgresql.org
X-Spam-Relay-Country:
Subject: Re: Change in 9.1?
X-Spam-Flag: No
X-SA-Exim-Version: 4.2.1 (built Fri, 06 Aug 2010 16:31:04 -0600)
X-SA-Exim-Scanned: Yes (on in02.mta.xmission.com)
X-Archive-Number: 201111/74
X-Sequence-Number: 36047
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Change in 9.1?
@ 2011-11-22 15:52 David Johnston <polobo@yahoo.com>
parent: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2011-11-22 15:52 UTC (permalink / raw)
To: pgsql-sql
-----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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-22 15:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-22 07:39 Change in 9.1? Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-11-22 11:54 ` Andreas Joseph Krogh <andreak@officenet.no>
2011-11-22 15:52 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox