Re: Query performance strangeness.. - Mailing list pgsql-general

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