Steve Spicklemire wrote:
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?
Well, since you've got an index using it, you *must* have already marked
it immutable. Presumably it's not genuinely immutable though.
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;
That might be stable, but I don't see how it could be immutable unless
the xml is in your "people" table.
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))
Filter: (('STJ'::text =
get_cem_for_directburial(personid)) AND ((status)::text <> 'R'::text)
AND ((status)::text <> 'F'::text))
Filter: (('HCC'::text =
get_cem_for_directburial(personid)) AND ((status)::text <> 'R'::text)
AND ((status)::text <> 'F'::text))
If the query is actually calling get_cem_for_directburial during the
query... then I'm sunk. I'll have to move that data to a regular indexed
field. I can do that... it's just a lot of work. ;-(
Where you see it calling "Filter" it's evaluating the function I'm
afraid. It's possible for the executor to call the function more than
once too.
You've got so much data hidden behind functions, it's unlikely you'll
get the planner to make any informed decisions as to the quickest plan.
You're testing for inequality on status, so unless <> F / R is uncommon
a partial query on that probably won't help much either.
Just to be sure, cheat. Run get_cem_for_directburial() over the whole
people table and dump the results into a cache table. Then, try the same
query with a function that just does a table lookup - see if that makes
things faster.
Then I'd just write a couple of trigger functions to keep the cache
table up-to-date and join against it. That will let the planner see
common values and make better predictions for its plans.
If you (or anyone else) would like to explore functional indexes and
calling, I've attached a small demo script.
--
Richard Huxton
Archonet Ltd
BEGIN;
CREATE TABLE fit (
a SERIAL,
b int4,
PRIMARY KEY (a)
);
CREATE SEQUENCE dummy_seq;
CREATE FUNCTION fit_fn(id int4) RETURNS int4 AS $$
BEGIN
PERFORM nextval('dummy_seq');
RETURN (id % 100);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE INDEX fit_fn_idx ON fit ( fit_fn(a) );
INSERT INTO fit SELECT generate_series(1,1000), round(random() * 100);
COMMIT;
VACUUM ANALYSE fit;
SELECT nextval('dummy_seq');
EXPLAIN ANALYSE SELECT a,b FROM fit WHERE fit_fn(a) = 7;
SELECT nextval('dummy_seq');
EXPLAIN ANALYSE SELECT a,b FROM fit WHERE fit_fn(a) = b;
SELECT nextval('dummy_seq');
