From: Doug Y <dylists@ptd.net>
To: pgsql-sql@postgresql.org
Subject: OR clause causing strange index performance
Date: Thu, 20 May 2004 13:32:21 -0400
Message-ID: <6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com> (raw)
Hello,
For the following query:
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'
(lists table has ~ 500k records, users ~ 100k, permissions ~ 60k, user_list
~ 530k)
lists can be associated with 2 users via the user_list table, and are
designated by the 1 or 2, can have a user with a 1, a user with a 2 or one
of each.
I'm getting really poor performance... about 60 seconds. Explain (see
below) is showing its trying to use the pkey (list_id1,list_id2) on the
list table, but not showing an index condition.
If I get rid of the OR, and only at one of the conditions it returns very
quickly and properly set the index condition. I can't use a union because I
would end up with duplicate rows for those that have both ul type 1 and 2
I actually started off trying the query by looking at lists first, but
performance was awful since I can't narrow down the records like I can with
permissions.
I know the tables aren't really set up ideally, and I actually have to join
a few more tables to the lists table after the fact, but want to get the
base running as efficient as possible first.
Is there any way to get this query to use the correct index condition so
that it runs in a reasonable amount of time?
Thanks!
EXPLAIN with the OR
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..13051262.13 rows=1 width=1794)
Join Filter: ((("inner".list_id1 = "outer".list_id1) OR
("inner".list_id1 = "outer".list_id1)) AND (("inner".list_id2 =
"outer".list_id2) OR ("inner".list_id1 = "outer".list_id1)) AND
(("inner".list_id1 = "outer".list_id1) OR ("inner".list_id2 =
"outer".list_id2)) AND (("inner".list_id2 = "outer".list_id2) OR
("inner".list_id2 = "outer".list_id2)))
-> Nested Loop (cost=0.00..2654.08 rows=12 width=1087)
Join Filter: ("inner".type = '2'::character varying)
-> Nested Loop (cost=0.00..427.39 rows=12 width=1032)
Join Filter: ("inner".type = '1'::character varying)
-> Nested Loop (cost=0.00..23.82 rows=2 width=977)
-> Index Scan using permissions_pkey on permissions
p (cost=0.00..12.14 rows=2 width=476)
Index Cond: ((code = '123456'::character
varying) AND (type = 'User'::character varying))
-> Index Scan using users_pkey on users
u (cost=0.00..4.92 rows=1 width=501)
Index Cond: (u.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul1 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul1.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul2 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul2.id = "outer".id)
-> Seq Scan on lists 1 (cost=0.00..26103.61 rows=508361 width=707)
(16 rows)
Note: this example shows it trying a seq scan.. I've tried it with
enable_seqscan off, too. When I referred above to it trying to use index
scan, it was from an explain with an additional join to the lists table after:
-> Index Scan using lists_pkey on lists l (cost=0.00..1872375.82
rows=508361 width=144)
EXPLAIN without the OR
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..2740.09 rows=17 width=1794)
-> Nested Loop (cost=0.00..2654.08 rows=12 width=1087)
Join Filter: ("inner".type = '2'::character varying)
-> Nested Loop (cost=0.00..427.39 rows=12 width=1032)
Join Filter: ("inner".type = '1'::character varying)
-> Nested Loop (cost=0.00..23.82 rows=2 width=977)
-> Index Scan using permissions_pkey on permissions
p (cost=0.00..12.14 rows=2 width=476)
Index Cond: ((code = '123456'::character
varying) AND (type = 'User'::character varying))
-> Index Scan using users_pkey on users
u (cost=0.00..4.92 rows=1 width=501)
Index Cond: (u.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul1 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul1.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul2 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul2.id = "outer".id)
-> Index Scan using list_pkey on lists 1 (cost=0.00..6.40 rows=1
width=707)
Index Cond: ((l.list_id1 = "outer".list_id1) AND (l.list_id2 =
"outer".list_id2))
(16 rows)
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: dylists@ptd.net
Subject: Re: OR clause causing strange index performance
In-Reply-To: <6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com>
* 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