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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: building 7.4.1 (on linux) with --disable-shared
Следующее
От: Mike Mascari
Дата:
Сообщение: Re: Moving from MySQL to PGSQL....some questions (multilevel