array can be slow when joining?

Поиск
Список
Период
Сортировка
От Volodymyr Kostyrko
Тема array can be slow when joining?
Дата
Msg-id 4C7A5E1E.6050205@gmail.com
обсуждение исходный текст
Ответы Re: array can be slow when joining?  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-performance
I don't clearly understand why this happens, but when I try join some
tables using arrays I end up with:

=# explain select count(*) from urls JOIN rules ON urls.tag && rules.tag;
                                       QUERY PLAN

---------------------------------------------------------------------------------------
  Aggregate  (cost=1356.27..1356.28 rows=1 width=0)
    ->  Nested Loop  (cost=20.33..1354.96 rows=523 width=0)
          ->  Seq Scan on rules  (cost=0.00..1.01 rows=1 width=37)
          ->  Bitmap Heap Scan on urls  (cost=20.33..1347.42 rows=523
width=29)
                Recheck Cond: (urls.tag && rules.tag)
                ->  Bitmap Index Scan on url_tag_g  (cost=0.00..20.20
rows=523 width=0)
                      Index Cond: (urls.tag && rules.tag)

Here tag is text[] with list of tags. Whole select takes 142 ms. It
drops down to 42 ms when I add some conditions that strip result table
to zero length.

What am I missing? Is there any other ways to overlap those ones? Or
should I find "any other way"?

--
Sphinx of black quartz judge my vow.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jose Ildefonso Camargo Tolosa
Дата:
Сообщение: Re: Performance on new 64bit server compared to my 32bit desktop
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: array can be slow when joining?