Re: Performs WAY better with enable_seqscan = off

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux