X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 4BFCAD1B1CF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 May 2004 12:34:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 67472-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 May 2004 12:34:23 -0300 (ADT)
Received: from atrtnet.hu (fw.i-trade.hu [212.92.1.62])
	by svr1.postgresql.org (Postfix) with SMTP id CC7B7D1B273
	for <pgsql-sql@postgresql.org>; Fri, 28 May 2004 12:34:20 -0300 (ADT)
Received: (qmail 28649 invoked from network); 28 May 2004 15:11:57 -0000
Received: from unknown (HELO atesz) (212.92.7.130)
	by 0 with SMTP; 28 May 2004 15:11:57 -0000
From: "Atesz" <atesz@ritek.hu>
To: "'Doug Y'" <dylists@ptd.net>, <pgsql-sql@postgresql.org>
Subject: Re: OR clause causing strange index performance
Date: Fri, 28 May 2004 17:37:20 +0200
Message-ID: <000b01c444c9$ad437110$0b02010a@atesz>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1409
In-Reply-To: <6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com>
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200405/218
X-Sequence-Number: 17932

Hi!

I read your JOIN - Index Scaning - OR problem. I don't understand why
you decomposed JOINs two brach (ul1 and ul2).
If I understand your problem well I can suggest the next idea for your
QUERY (you don't need two branch):

SELECT *  FROM  permissions p
  INNER JOIN users u       ON u.id  = p.id
  INNER JOIN user_list ul  ON ul.id = u.id
  INNER JOIN lists l          ON ( l.list_id1 = ul.list_id1 AND
l.list_id2 = ul.list_id2 )
WHERE 
  (ul.type = '1' OR ul.type= '2') and p.code = '123456' AND p.type =
'User';

If ul.type field is integer you can optimze the OR (which can cause
index scan problem and low performance) with BETWEEN:

SELECT *  FROM  permissions p
  INNER JOIN users u       ON u.id  = p.id
  INNER JOIN user_list ul  ON ul.id = u.id
  INNER JOIN lists l          ON ( l.list_id1 = ul.list_id1 AND
l.list_id2 = ul.list_id2 )
WHERE 
  ul.type BETWEEN 1 AND 2   and p.code = '123456' AND p.type = 'User';

After that you need some good index on ul.type, p.code and p.type. You
have to think about creating indices. Analyse the results of explain!!!
In my opinion this solution may be very fast.

Regards,
Antal Attila


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Doug Y
Sent: Thursday, May 20, 2004 7:32 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] OR clause causing strange index performance


SELECT *
   FROM permissions p
        INNER JOIN users u
                ON u.id = p.id
        LEFT JOIN user_list ul1
               ON ul1.id = u.id
                  AND ul1.type = '1'
        LEFT JOIN user_list ul2
               ON ul2.id = u.id
                  AND ul2.type = '2'
        INNER JOIN lists l
                ON ( l.list_id1 = ul1.list_id1 AND l.list_id2 =
ul1.list_id2 )
                     OR
                   ( l.list_id1 = ul2.list_id1 AND l.list_id2 =
ul2.list_id2 )
  WHERE
        p.code = '123456' AND p.type = 'User'