Re: Performs WAY better with enable_seqscan = off

Поиск
Список
Период
Сортировка
От Ragnar
Тема Re: Performs WAY better with enable_seqscan = off
Дата
Msg-id 1148208611.28240.101.camel@localhost.localdomain
обсуждение исходный текст
Ответ на Performs WAY better with enable_seqscan = off  (Brendan Duddridge <brendan@clickspace.com>)
Ответы Re: Performs WAY better with enable_seqscan = off  (Brendan Duddridge <brendan@clickspace.com>)
Список pgsql-performance
On sun, 2006-05-21 at 02:21 -0600, Brendan Duddridge wrote:
> Hi,
>
>
> I have a query that performs WAY better when I have enable_seqscan =
> off:
>
>
> explain analyze select ac.attribute_id, la.name, ac.sort_order from
> attribute_category ac, localized_attribute la where ac.category_id =
> 1001402 and la.locale_id = 1000001 and ac.is_browsable = 'true' and
> la.attribute_id = ac.attribute_id and exists ( select 'x' from
> product_attribute_value pav, category_product cp where (pav.product_id
> || '.' || pav.attribute_id) = (cp.product_id || '.' ||
> ac.attribute_id) and pav.status_code is null and (cp.category_id ||
> '.' || cp.is_visible) = '1001402.true') order by (ac.sort_order is
> null), ac.sort_order, la.name asc;

is there some reason for the complicated form of the
join conditions in the subselect?

would this not be clearer:

explain analyze
  select ac.attribute_id,
         la.name,
         ac.sort_order
  from attribute_category ac,
       localized_attribute la
  where ac.category_id = 1001402
        and la.locale_id = 1000001
        and ac.is_browsable = 'true'
        and la.attribute_id = ac.attribute_id
        and exists
          (select 'x' from product_attribute_value pav,
                           category_product cp
           where pav.product_id = cp.product_id
                 and pav.attribute_id = ac.attribute_id
                 and pav.status_code is null
                 and cp.category_id= '1001402'
                 and cp.is_visible = 'true'
          )
   order by (ac.sort_order is null),
             ac.sort_order,
             la.name asc;


possibly the planner would have a better time
figuring out if any indexes are usable or estimating
the subselect rowcount

gnari



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

Предыдущее
От: Brendan Duddridge
Дата:
Сообщение: Performs WAY better with enable_seqscan = off
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: Benchmarking Function