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 > rows).
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 > possible) 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") http://linuxdatabases.info/~cbbrowne/postgresql.html 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." <http://www.eviloverlord.com/> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
