Обсуждение: 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) >= 'jonesAAAAAAAAAAAAAAAAAAAAAAAAA'::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 = ("inner".intermentspaceroleid)::text) Filter: ((("outer".status)::text = 'D'::text) OR (("inner".name)::text = 'burial'::text)) -> Nested Loop Left Join (cost=0.00..147724.82 rows=1472 width=61) (actual time=8.824..91517.141 rows=657 loops=1) -> 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)) -> Index Scan using idx_intermentspacepersons_pers on intermentspacepersons isp (cost=0.00..22.24 rows=6 width=33) (actual time=0.353..0.452 rows=3 loops=256) Index Cond: (("outer".personid)::text = (isp.personid)::text) -> Seq Scan on intermentspaceroles isr (cost=0.00..1.20 rows=20 width=19) (actual time=0.003..0.047 rows=20 loops=657) Total runtime: 91584.769 ms Next I change the sort order of the result, just use last, first instead of lc3key: CCOC=# explain analyze select * from ISpacePersonRoles where ('HCC' = get_cem_for_directBurial(personid) AND lc3key >= lower_concat3 ('Jones', '', '') and (status = 'D' or role = 'burial') and status <> 'R' and status <> 'F') order by last, first asc limit 100; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------- Limit (cost=19031.87..19032.12 rows=100 width=62) (actual time=55.845..56.418 rows=100 loops=1) -> Sort (cost=19031.87..19035.55 rows=1472 width=62) (actual time=55.839..56.032 rows=100 loops=1) Sort Key: p."last", p."first" -> Hash Left Join (cost=12.27..18954.42 rows=1472 width=62) (actual time=37.250..54.048 rows=269 loops=1) Hash Cond: (("outer".roleid)::text = ("inner".intermentspaceroleid)::text) Filter: ((("outer".status)::text = 'D'::text) OR (("inner".name)::text = 'burial'::text)) -> Nested Loop Left Join (cost=11.02..18916.37 rows=1472 width=61) (actual time=0.370..44.809 rows=2157 loops=1) -> Bitmap Heap Scan on people p (cost=11.02..1976.46 rows=759 width=40) (actual time=0.339..14.824 rows=759 loops=1) Recheck Cond: ('HCC'::text = get_cem_for_directburial(personid)) Filter: ((lower_concat3("last", "first", (((middle)::text || (personid)::text))::character varying) >= 'jonesAAAAAAAAAAAAAAAAAAAAAAAAA'::text) AND ((status)::text <> 'R'::text) AND ((status)::text <> 'F'::text)) -> Bitmap Index Scan on idx_people_cemid (cost=0.00..11.02 rows=1433 width=0) (actual time=0.303..0.303 rows=1415 loops=1) Index Cond: ('HCC'::text = get_cem_for_directburial(personid)) -> Index Scan using idx_intermentspacepersons_pers on intermentspacepersons isp (cost=0.00..22.24 rows=6 width=33) (actual time=0.016..0.023 rows=3 loops=759) Index Cond: (("outer".personid)::text = (isp.personid)::text) -> Hash (cost=1.20..1.20 rows=20 width=19) (actual time=0.121..0.121 rows=20 loops=1) -> Seq Scan on intermentspaceroles isr (cost=0.00..1.20 rows=20 width=19) (actual time=0.005..0.054 rows=20 loops=1) Total runtime: 56.720 ms Fine! .056 seconds! Now try 'STJ' again... using last, first: 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 last, first asc limit 100; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------------------------- Limit (cost=64448.05..64448.30 rows=100 width=62) (actual time=36866.189..36866.771 rows=100 loops=1) -> Sort (cost=64448.05..64660.71 rows=85065 width=62) (actual time=36866.183..36866.400 rows=100 loops=1) Sort Key: p."last", p."first" -> Hash Left Join (cost=49906.15..54260.22 rows=85065 width=62) (actual time=33936.104..36283.328 rows=27078 loops=1) Hash Cond: (("outer".roleid)::text = ("inner".intermentspaceroleid)::text) Filter: ((("outer".status)::text = 'D'::text) OR (("inner".name)::text = 'burial'::text)) -> Merge Left Join (cost=49904.90..52132.35 rows=85065 width=61) (actual time=33913.184..35665.407 rows=107598 loops=1) Merge Cond: ("outer"."?column6?" = "inner"."? column4?") -> Sort (cost=8747.88..8857.56 rows=43872 width=40) (actual time=27311.087..27414.262 rows=45700 loops=1) Sort Key: (p.personid)::text -> Bitmap Heap Scan on people p (cost=808.33..4562.19 rows=43872 width=40) (actual time=499.366..26080.114 rows=45700 loops=1) Recheck 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)) -> Bitmap Index Scan on idx_people_lower_concat3_last (cost=0.00..808.33 rows=63522 width=0) (actual time=498.285..498.285 rows=66126 loops=1) Index Cond: (lower_concat3 ("last", "first", (((middle)::text || (personid)::text))::character varying) >= 'jonesAAAAAAAAAAAAAAAAAAAAAAAAA'::text) -> Sort (cost=41157.02..41735.74 rows=231487 width=33) (actual time=6601.686..7115.285 rows=231484 loops=1) Sort Key: (isp.personid)::text -> Seq Scan on intermentspacepersons isp (cost=0.00..5440.87 rows=231487 width=33) (actual time=10.585..906.115 rows=231487 loops=1) -> Hash (cost=1.20..1.20 rows=20 width=19) (actual time=22.801..22.801 rows=20 loops=1) -> Seq Scan on intermentspaceroles isr (cost=0.00..1.20 rows=20 width=19) (actual time=22.668..22.717 rows=20 loops=1) Total runtime: 36885.930 ms Huh? How can this happen? Where am I getting lost here? thanks for any thoughts/input! -steve
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
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" Column | Type | Modifiers ----------------------+----------------------------- +----------------------------------------------------------------------- -------------------------- intermentspaceroleid | character varying(40) | not null default ('AUTO'::text || (nextval (('IntermentSpaceRoles_seq'::text)::regclass))::text) modified | timestamp without time zone | default now() created | timestamp without time zone | default now() enabled | boolean | name | character varying(30) | label | character varying(30) | reverseroleid | character varying(30) | objecttype | character varying(30) | Indexes: "intermentspaceroles_pkey" PRIMARY KEY, btree (intermentspaceroleid) > >> -> 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. > 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. ;-( thanks for your help! -steve
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');