Re: [PERFORM] Slow functional indexes?
Stuart Bishop wrote:
I would like to understand what causes some of my indexes to be slower to
use than others with PostgreSQL 8.1. On a particular table, I have an int4
primary key, an indexed unique text 'name' column and a functional index of
type text. The function (person_sort_key()) is declared IMMUTABLE and
RETURNS NULL ON NULL INPUT.
A simple query ordering by each of these columns generates nearly identical
query plans, however runtime differences are significantly slower using the
functional index. If I add a new column to the table containing the result
of the function, index it and query ordering by this new column then the
runtime is nearly an order of magnitude faster than using the functional
index (and again, query plans are nearly identical).
(The following log is also at
http://rafb.net/paste/results/vKVuyi47.nln.html if that is more readable)
Here is a minimal test case that demonstrates the issue. Can anyone else
reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at
the end, the one that orders by a user created IMMUTABLE stored procedure is
consistently slower than the other three variants.
BEGIN;
DROP TABLE TestCase;
COMMIT;
ABORT;
BEGIN;
CREATE TABLE TestCase (name text, alt_name text);
CREATE OR REPLACE FUNCTION munge(s text) RETURNS text
IMMUTABLE RETURNS NULL ON NULL INPUT
LANGUAGE plpgsql AS $$
BEGIN
RETURN lower(s);
END;
$$;
-- Fill the table with random strings
CREATE OR REPLACE FUNCTION fill_testcase(num_rows int) RETURNS boolean
LANGUAGE plpgsql AS
$$
DECLARE
row_num int;
char_num int;
name text;
BEGIN
FOR row_num IN 1..num_rows LOOP
name := '';
FOR char_num IN 1..round(random() * 100) LOOP
name := name || chr((
round(random() * (ascii('z') - ascii('!'))) + ascii('!')
)::int);
END LOOP;
INSERT INTO TestCase VALUES (name, lower(name));
IF row_num % 2 = 0 THEN
RAISE NOTICE '% of % rows inserted', row_num, num_rows;
END IF;
END LOOP;
RETURN TRUE;
END;
$$;
SELECT fill_testcase(50);
CREATE INDEX testcase__name__idx ON TestCase(name);
CREATE INDEX testcase__lower__idx ON TestCase(lower(name));
CREATE INDEX testcase__munge__idx ON TestCase(munge(name));
CREATE INDEX testcase__alt_name__idx ON TestCase(alt_name);
COMMIT;
ANALYZE TestCase;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY name;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY lower(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY munge(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY alt_name;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY name;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY lower(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY munge(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY alt_name;
--
Stuart Bishop [EMAIL PROTECTED]
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] Slow functional indexes?
Stuart Bishop [EMAIL PROTECTED] writes: Here is a minimal test case that demonstrates the issue. Can anyone else reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at the end, the one that orders by a user created IMMUTABLE stored procedure is consistently slower than the other three variants. Wow, interesting. I'm surprised we never realized this before, but here's the deal: the generated plan computes the ORDER BY expressions even if we end up not needing them because the ordering is created by an indexscan rather than an explicit sort step. (Such a sort step would of course need the values as input.) So the differential you're seeing represents the time for all those useless evaluations of the function. The difference in the estimated cost comes from that too --- the code doing the estimation can see perfectly well that there's an extra function call in the plan ... Not sure whether there's a simple way to fix this; it might take some nontrivial rejiggering in the planner. Or maybe not, but I don't have any cute ideas about it at the moment. I wonder whether there are any other cases where we are doing useless computations of resjunk columns? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Slow functional indexes?
I have a varchar field which is most commonly queried like someField like '%abcd'. Realizing that it wouldn't be able to use an index for this type of query I created a reverse() function and an index using the function reverse(someField) so that the query would be performed as reverse(someField) like reverse('%abcd'). When I looked at the query plan it seemed like it was using the new reverse index properly but also seemed to run slower. Would this explain these bazaar results? I have since gone back to the method without using the reverse function. Thanks
On 11/5/06, Tom Lane [EMAIL PROTECTED] wrote:
Stuart Bishop [EMAIL PROTECTED] writes: Here is a minimal test case that demonstrates the issue. Can anyone else reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at
the end, the one that orders by a user created IMMUTABLE stored procedure is consistently slower than the other three variants.Wow, interesting.I'm surprised we never realized this before, but
here's the deal: the generated plan computes the ORDER BY expressionseven if we end up not needing them because the ordering is created byan indexscan rather than an explicit sort step.(Such a sort step would
of course need the values as input.)So the differential you're seeingrepresents the time for all those useless evaluations of the function.The difference in the estimated cost comes from that too --- the code
doing the estimation can see perfectly well that there's an extrafunction call in the plan ...Not sure whether there's a simple way to fix this; it might take somenontrivial rejiggering in the planner.Or maybe not, but I don't have
any cute ideas about it at the moment.I wonder whether there are any other cases where we are doing uselesscomputations of resjunk columns?regards, tom lane---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
-- Gene Hartcell: 443-604-2679
Re: [PERFORM] Setting nice values
On Nov 2, 2006, at 9:14 AM, Madison Kelly wrote: I've got a script (perl, in case it matters) that I need to run once a month to prepare statements. This script queries and updates the database a *lot*. I am not concerned with the performance of the SQL calls so much as I am about the impact it has on the server's load. Is there a way to limit queries speed (ie: set a low 'nice' value on a query)? This might be an odd question, or I could be asking the question the wrong way, but hopefully you the idea. :) The BizGres folks have been working on resource queuing, which will eventually do what you want. Take a look at the BizGres mailing list archives for more info. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
