Re: functional index not used, looping simpler query just faster

On Thu, Jul 10, 2008 at 11:40:40AM +0200, Ivan Sergio Borgonovo wrote:
> I've this:

What's basically killing you is this condition:
>   select i2.ItemID from catalog_items i2
>     inner join catalog_brands b2 on upper(i2.brands)=upper(b2.name)
>     where i1.brands=i2.brands   <*********
>     and i2.dataPub>(now() - interval '8 month') and

Is not indexable. Hence the seqscan, which makes everything slow.
In your "faster" version you test against a condition which *is*
indexable, hence it's faster.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.