The world rejoiced as [EMAIL PROTECTED] (Mark Aufflick) wrote:
> Hi All,
> I have boiled my situation down to the following simple case:
> (postgres version 7.3)
> * Query 1 is doing a sequential scan over a table (courtesy of field
> ILIKE 'foo%') and index joins to a few others
> * Query 2 is doing a functional index scan over the same table
> (lower(field) LIKE 'foo%') and index joins to a few others
> * neither query has an order by clause
> * for the purpose of testing, both queries are designed to return the
> same result set
> Obviously Q2 is faster than Q1, but if I ever run them both at the
> same time (lets say I run two of Q1 and one of Q2 at the same time)
> then Q2 consistently returns WORSE times than Q1 (explain analyze
> confirms that it is using the index).
> My assumption is that the sequential scan is blowing the index from
> any cache it might live in, and simultaneously stealing all the disk
> IO that is needed to access the index on disk (the table has 200,000
There's something to be said for that...
> If I simplify the case to not do the index joins (ie. operate on the
> one table only) the situation is not as dramatic, but similar.
> My thoughts are:
> 1) kill the sequential scan - but unfortunately I don't have direct
> control over that code
This is a good choice, if plausible...
> 2) change the way the server allocates/prioritizes different caches -
> i don't know enough about how postgres caches work to do this (if it's
That's what the 8.0 cache changes did... Patent claim issues are
leading to some changes to the prioritization, which is liable to
change 8.0.something and 8.1.
> 3) try it on postgres 7.4 - possible, but migrating the system to 7.4
> in production will be hard because the above code that I am not
> responsible for has a lot of (slightly wacky) implicit date casts
Moving to 7.4 wouldn't materially change the situation; you'd have to
go all the way to version 8.
(format nil "[EMAIL PROTECTED]" "cbbrowne" "gmail.com")
Rules of the Evil Overlord #32. "I will not fly into a rage and kill a
messenger who brings me bad news just to illustrate how evil I really
am. Good messengers are hard to come by."
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?