Re: [GENERAL] Query performance strangeness..
Hi Richard,
On Jul 19, 2007, at 12:49 AM, Richard Huxton wrote:
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.
Here is the function body... the data is stored in and XML pickle.
I had hoped that it would only be called in building the index.
Since the query uses it in the 'filter' step.. I'm not sure if it's
using the index or not.
Does marking the function immutable help the planner know whether it
can use the index or not?
CCOC=# \df+ get_cem_for_directBurial
List of functions
Schema | Name | Result data type | Argument
data types | Owner | Language | Source code | Description
+--+--
+-+-+--+-+-
public | get_cem_for_directburial | text | character
varying | webuser | plpgsql |
DECLARE
personID ALIAS for $1;
qResult RECORD;
BEGIN
SELECT INTO qResult get_xml_value('/params/param/value/
struct/member/*[contains(text(),''cemid'')]/parent::*/value/string/
text()','People',personID,'') as cem;
return qResult.cem;
END;
|
(1 row)
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.
CCOC=# \d people
Table
public.people
Column |Type
| Modifiers
+-
+---
-
personid | character varying(40) | not null default
('AUTO'::text || (nextval(('People_seq'::text)::regclass))::text)
modified | timestamp without time zone | default now()
created| timestamp without time zone | default now()
enabled| boolean |
first | character varying(40) |
middle | character varying(15) |
last | character varying(80) |
gender | character varying(2)|
sbirthdate | character varying(30) |
sdeathdate | character varying(30) |
status | character varying(30) |
Indexes:
people_pkey PRIMARY KEY, btree (personid)
idx_people_cemid btree (get_cem_for_directburial(personid))
idx_people_lower_concat3_last btree (lower_concat3(last,
first, (middle::text || personid::text)::character varying))
idx_people_servicenum btree
(get_numeric_servicenumber_for_personid(personid))
idx_people_status btree (status)
idx_people_take4_personid_ btree (take4(personid))
CCOC=# \d intermentspacepersons
Table
public.intermentspacepersons
Column |Type
| Modifiers
+-
+---
intermentspacepersonid | character varying(40) | not null
default ('AUTO'::text || (nextval
(('IntermentSpacePersons_seq'::text)::regclass))::text)
modified | timestamp without time zone | default now()
created| timestamp without time zone | default now()
enabled| boolean |
objectid | character varying(30) |
personid | character varying(30) |
roleid | character varying(30) |
Indexes:
idx_intermentspacepersons_obje btree (objectid)
idx_intermentspacepersons_pers btree (personid)
CCOC=# \d intermentspaceroles
Table
public.intermentspaceroles
[GENERAL] Query performance strangeness..
Hi Folks,
I'm new to this list, but I've been using postgresql for a few years.
In general I've been able to figure things out by reading various
docs. I've hit something now that I haven't been able to sort out at
all. It may be that there's some document that explains all this...
if so, please point it out!
I have a view called ISpacePersonRoles that joins my 'people' table
with spaces and roles:
CCOC=# \d ISpacePersonRoles
View public.ispacepersonroles
Column | Type | Modifiers
--+---+---
lc3key | text |
personid | character varying(40) |
last | character varying(80) |
first| character varying(40) |
middle | character varying(15) |
status | character varying(30) |
intermentspaceid | character varying(30) |
role | character varying(30) |
View definition:
SELECT lower_concat3(p.last, p.first, (p.middle::text ||
p.personid::text)::character varying) AS lc3key, p.personid,
p.last, p.first, p.middle, p.status, isp.objectid AS
intermentspaceid, isr.name AS role
FROM people p
LEFT JOIN intermentspacepersons isp ON p.personid::text =
isp.personid::text
LEFT JOIN intermentspaceroles isr ON isp.roleid::text =
isr.intermentspaceroleid::text;
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.)
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:
CCOC=# explain analyze select * from ISpacePersonRoles where ('STJ' =
get_cem_for_directBurial(personid) AND lc3key = lower_concat3
('Jones', '', '') and (status = 'D' or role = 'burial') and status
'R' and status 'F') order by lc3key asc limit 100;
QUERY PLAN
Limit (cost=1.22..1361.55 rows=100 width=62) (actual
time=2.172..90.077 rows=100 loops=1)
- Nested Loop Left Join (cost=1.22..1157163.90 rows=85065
width=62) (actual time=2.167..89.682 rows=100 loops=1)
Join Filter: ((outer.roleid)::text =
(inner.intermentspaceroleid)::text)
Filter: (((outer.status)::text = 'D'::text) OR
((inner.name)::text = 'burial'::text))
- Nested Loop Left Join (cost=0.00..1109951.60
rows=85065 width=61) (actual time=0.436..54.552 rows=374 loops=1)
- 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) =
'jonesA'::text)
Filter: (('STJ'::text = get_cem_for_directburial
(personid)) AND ((status)::text 'R'::text) AND ((status)::text
'F'::text))
- Index Scan using idx_intermentspacepersons_pers
on intermentspacepersons isp (cost=0.00..22.24 rows=6 width=33)
(actual time=0.024..0.030 rows=2 loops=171)
Index Cond: ((outer.personid)::text =
(isp.personid)::text)
- Materialize (cost=1.22..1.42 rows=20 width=19) (actual
time=0.002..0.042 rows=20 loops=374)
- Seq Scan on intermentspaceroles isr
(cost=0.00..1.20 rows=20 width=19) (actual time=0.005..0.060 rows=20
loops=1)
Total runtime: 90.395 ms
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!
Limit (cost=0.00..10191.16 rows=100 width=62) (actual
time=8.909..91584.430 rows=100 loops=1)
- Nested Loop Left Join (cost=0.00..150013.78 rows=1472
width=62) (actual time=8.905..91583.951 rows=100 loops=1)
Join Filter: ((outer.roleid)::text =
