Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6830464FCDF for ; Wed, 5 Nov 2008 07:12:37 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49677-06 for ; Wed, 5 Nov 2008 07:12:36 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.248]) by mail.postgresql.org (Postfix) with ESMTP id 0708064FCC9 for ; Wed, 5 Nov 2008 07:12:35 -0400 (AST) Received: by an-out-0708.google.com with SMTP id d14so436579and.28 for ; Wed, 05 Nov 2008 03:12:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=1jzqdr9ZtMANZXjYvj5afEp0wfpmCGLN7kJlS1XCh8s=; b=xmvg9pK9+5h66ArgnCbSszE+gw/tzTVJM2SRxdo8IAu+5P2CtZxLE+BbrxSzQ3SuQ5 ciA3b/pcMqV63TSuqtcCPc+S5EqwG36XycEIMjzuzhrZDNeRFHkv0UvfTnh2M6pdfc8x DtTMnN1wGMyyh5yvLYXC/XBeJZPE5qMQpICs4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=JG4sGmLCxsi3/OyNsWsVW6sSFVZ60C2eelFx50PBLE87LWdHwVJ5aLOwQblJ8tSvcf 7ptCuYBHZavmFIwPz2K0m1g6TCYvUsXiybY9VQwEYZl2FwdvR7PDW49O9jVMvkYAxn7P mLwQi1Gni/SrlkTuKhAmpJPPIZfUag2Xk1gzk= Received: by 10.65.73.16 with SMTP id a16mr572279qbl.89.1225883552489; Wed, 05 Nov 2008 03:12:32 -0800 (PST) Received: by 10.64.142.18 with HTTP; Wed, 5 Nov 2008 03:12:32 -0800 (PST) Message-ID: <331e40660811050312u552cab53ifa88e493e1c4f7ac@mail.gmail.com> Date: Wed, 5 Nov 2008 13:12:32 +0200 From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" To: pgsql-performance@postgresql.org Subject: PostgreSQL OR performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4359_22483484.1225883552627" X-Archive-Number: 200811/7 X-Sequence-Number: 31738 ------=_Part_4359_22483484.1225883552627 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello. For a long time already I can see very poor OR performance in postgres. If one have query like "select something from table where condition1 or condition2" it may take ages to execute while "select something from table where condition1" and "select something from table where condition2" are executed very fast and "select something from table where condition1 and not condition2 union all select something from table where condition2" gives required results fast For example, in my current query for "condition1" optimizer gives 88252, for "condition1 and not condition2" it is 88258, for "condition2" it is 99814. And for "condition1 or condition2" it is 961499627680. And it does perform this way. All is more or less good when "select" part is easy and query can be easily rewritten. But for complex queries it looks ugly and if the query is autogenerated, moving autogeneration mechanism from creating simple clean "where" to unions is not an easy task. So the question is: Do I miss something? Can this be optimized? ------=_Part_4359_22483484.1225883552627 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello.

For a long time already I can see very poor OR performance in postgres.
If one have query like "select something from table where condition1 or condition2" it may take ages to execute while
"select something from table where condition1" and "select something from table where condition2" are executed very fast and
"select something from table where condition1 and not condition2 union all select something from table where condition2" gives required results fast

For example, in my current query for "condition1" optimizer gives 88252, for "condition1 and not condition2" it is 88258, for "condition2" it is 99814.
And for "condition1 or condition2" it is 961499627680. And it does perform this way.

All is more or less good when "select" part is easy and query can be easily rewritten. But for complex queries it looks ugly and if the query is autogenerated, moving autogeneration mechanism from creating simple clean "where" to unions is not an easy task.

So the question is: Do I miss something? Can this be optimized?

------=_Part_4359_22483484.1225883552627--