OR clause causing strange index performance

From: Doug Y <dylists(at)ptd(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: OR clause causing strange index performance
Date: 2004-05-20 17:32:21
Message-ID: 6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Manuel Sugawara 2004-05-20 17:33:42 Simple view confuses PostgreSQL query planning
Previous Message LSanchez 2004-05-20 15:58:34 XML data field