Re: Query performance strangeness..

From: Richard Huxton <dev(at)archonet(dot)com>
To: Steve Spicklemire <steve(at)spvi(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Query performance strangeness..
Date: 2007-07-19 04:49:52
Message-ID: 469EED70.2000409@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2007-07-19 04:52:10 Re: Feature request: Per database search_path
Previous Message Steve Spicklemire 2007-07-19 03:47:10 Query performance strangeness..