Steve Spicklemire wrote:
I also have a function "get_cem_for_directBurial(personid)" that is
expensive to call, but it's also indexed, so I hoped that the index
would normally be used (essentially as a cache). It returns a 'cemetery
code' so I can search for folks buried in a particular cemetery. (The
cemetery code was added to a different table after the 'people' table
was more or less frozen.. I'd like to keep it that way if possible.)
How is this function defined? Is it marked "Immutable" or similar? The
body might be interesting too.
Sometimes I need to search for rows from the view that satisfy certain
criteria, sorted in some specific order. Here's where the trouble
starts. In the view I compute something I call 'lc3key', defined as:
lower_concat3(p."last", p."first", (p.middle::text ||
p.personid::text)::character varying) where 'lower_concat3' just returns
a lower case version of three strings all concatenated together. The
string is basically lastname, firstname, middle and personid (to
guarantee uniqueness). It seems like most of the time sorting by last,
first, middle should be the same as sorting by lc3key (all of these
things are indexed BTW). So here goes:
Definitions for the three tables and their indexes would be nice to
check against too.
-> Index Scan using idx_people_lower_concat3_last on
people p (cost=0.00..130784.91 rows=43872 width=40) (actual
time=0.366..47.016 rows=171 loops=1)
Index Cond: (lower_concat3("last", "first",
(((middle)::text || (personid)::text))::character varying) >=
'jonesAAAAAAAAAAAAAAAAAAAAAAAAA'::text)
Filter: (('STJ'::text =
get_cem_for_directburial(personid)) AND ((status)::text <> 'R'::text)
AND ((status)::text <> 'F'::text))
OK.. not too bad. If I do the same query... but ask for 'HCC' rather
than 'STJ', just a different cemetery code, I get 91 seconds... about
1000 times longer!
-> Index Scan using idx_people_lower_concat3_last on
people p (cost=0.00..130784.91 rows=759 width=40) (actual
time=8.722..91396.606 rows=256 loops=1)
Index Cond: (lower_concat3("last", "first",
(((middle)::text || (personid)::text))::character varying) >=
'jonesAAAAAAAAAAAAAAAAAAAAAAAAA'::text)
Filter: (('HCC'::text =
get_cem_for_directburial(personid)) AND ((status)::text <> 'R'::text)
AND ((status)::text <> 'F'::text))
In this case, look at the actual times. There are two possibilities:
1. The first query had its data/indexes in cache whereas the second
didn't. Run each three times in a row and see if the times stay roughly
constant.
2. Calls to get_cem_for_directburial() can vary widely in their
execution time.
--
Richard Huxton
Archonet Ltd
