performance problems: join conditions
От | Joseph Shraibman |
---|---|
Тема | performance problems: join conditions |
Дата | |
Msg-id | 4048145F.4070506@selectacast.net обсуждение исходный текст |
Список | pgsql-general |
I have a query like this: SELECT ... FROM u, d WHERE d.ukey = u.ukey AND <restrictions on u> AND (d.status = 3 OR (u.status = 3 AND d.status IN(2,5))); explain shows: -> Aggregate (cost=126787.04..126787.04 rows=1 width=4) -> Hash Join (cost=39244.00..126786.07 rows=387 width=4) Hash Cond: ("outer".ukey = "inner".ukey) Join Filter: (("outer".status = 3) OR ("inner".status = 3)) -> Seq Scan on u (cost=0.00..41330.30 rows=428294 width=6) Filter: ((podkey = 260) AND (NOT banned)) -> Hash (cost=33451.61..33451.61 rows=904156 width=6) -> Seq Scan on d (cost=0.00..33451.61 rows=904156 width=6) Filter: ((status = 2) OR (status = 5) OR (status = 3)) counts: d: status of 3: 1 total: 1026480 u: status of 3: 1080 total: 1531154 The query is trying to find entries where the status is 3 in one table or the other, but postgres won't use an index because it uses the status of 3 in the join condition. So it is using slow seqscans even though index queries would be much faster because the total number of entries where one or the other has status of 3 is small.
В списке pgsql-general по дате отправления: