From: Atesz <atesz@ritek.hu>
To: 'Doug Y' <dylists@ptd.net>
To: 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> (raw)
In-Reply-To: <6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com>
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'
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: atesz@ritek.hu, dylists@ptd.net
Subject: Re: OR clause causing strange index performance
In-Reply-To: <000b01c444c9$ad437110$0b02010a@atesz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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