Re: seq scan cache vs. index cache smackdown

The world rejoiced as mark(at)pumptheory(dot)com (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 "~S(at)~S" "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/>