OR clause causing strange index performance - Mailing list pgsql-sql

From Doug Y
Subject OR clause causing strange index performance
Date
Msg-id 6.0.1.1.2.20040520123807.01f2dec0@mail.traderonline.com
Whole thread Raw
Responses Re: OR clause causing strange index performance
Re: OR clause causing strange index performance
Re: OR clause causing strange index performance
List 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

----------------------------------------------------------------------------------------------------------------------------
NestedLoop  (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)
JoinFilter: ("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
Scanusing user_list_id on user_list 
 
ul1  (cost=0.00..159.86 rows=37 width=55)                     Index Cond: (ul1.id = "outer".id)         ->  Index Scan
usinguser_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.61rows=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

----------------------------------------------------------------------------------------------------------------------------
NestedLoop  (cost=0.00..2740.09 rows=17 width=1794)   ->  Nested Loop  (cost=0.00..2654.08 rows=12 width=1087)
JoinFilter: ("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
Scanusing user_list_id on user_list 
 
ul1  (cost=0.00..159.86 rows=37 width=55)                     Index Cond: (ul1.id = "outer".id)         ->  Index Scan
usinguser_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_pkeyon 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)




pgsql-sql by date:

Previous
From: Benoît BOURNON
Date:
Subject: Re: Replace function ...
Next
From: Manuel Sugawara
Date:
Subject: Simple view confuses PostgreSQL query planning