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).
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
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)
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
4) ask the fine people on the mailing list for other suggestions!
--
Mark Aufflick
e mark@pumptheory.com
w www.pumptheory.com (work)
w mark.aufflick.com (personal)
p +61 438 700 647
f +61 2 9436 4737
========================================================================
iBurst Wireless Broadband from $34.95/month www.platformnetworks.net
Forward undetected SPAM to: spam@mailsecurity.net.au
========================================================================
