X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6361FD1E0DA for ; Wed, 18 Feb 2004 15:25:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 62367-03 for ; Wed, 18 Feb 2004 11:25:12 -0400 (AST) Received: from yapok.nexcerpt.com (mail.nexcerpt.com [64.147.202.2]) by svr1.postgresql.org (Postfix) with ESMTP id B6AE0D1E0D4 for ; Wed, 18 Feb 2004 11:25:07 -0400 (AST) Received: from nexcerpt.com (DSL-NE-21.qtm.net [216.163.37.21]) (authenticated bits=0) by yapok.nexcerpt.com (8.12.8/8.12.8) with ESMTP id i1IFOp8W001486 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=OK); Wed, 18 Feb 2004 10:24:55 -0500 Message-ID: <403383A6.7090101@nexcerpt.com> Date: Wed, 18 Feb 2004 10:24:22 -0500 From: Jeff Boes Organization: Nexcerpt, Inc. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance Subject: Optimizer difference using function index between 7.3 and 7.4 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/207 X-Sequence-Number: 5731 We have a large (several million row) table with a field containing URLs. Now, funny thing about URLs: they mostly start with a common substring ("http://www."). But not all the rows start with this, so we can't just lop off the first N characters. However, we noticed some time ago that an index on this field wasn't as effective as an index on the REVERSE of the field. So ... CREATE OR REPLACE FUNCTION fn_urlrev(text) returns text as ' return reverse(lc($_[0])) ' language 'plperl' with (iscachable,isstrict); and then CREATE UNIQUE INDEX ix_links_3 ON links (fn_urlrev(path_base)); seemed to be much faster. When we have to look up a single entry in "links", we do so by something like -- SELECT * FROM links WHERE fn_urlrev(path_base) = ?; and it's rather fast. When we have a bunch of them to do, under 7.3 we found it useful to create a temporary table, fill it with reversed URLs, and join: INSERT INTO temp_link_urls VALUES (fn_urlrev(?)); SELECT l.path_base,l.link_id FROM links l JOIN temp_link_urls t ON (fn_urlrev(l.path_base) = t.rev_path_base); Here are query plans from the two versions (using a temp table with 200 rows, after ANALYZE on the temp table): 7.3: # explain select link_id from links l join clm_tmp_links t on (fn_urlrev(l.path_base) = t.rev_path_base); QUERY PLAN ----------------------------------------------------------------------------------------- Nested Loop (cost=0.00..3936411.13 rows=2000937 width=152) -> Seq Scan on clm_tmp_links t (cost=0.00..5.00 rows=200 width=74) -> Index Scan using ix_links_3 on links l (cost=0.00..19531.96 rows=10005 width=78) Index Cond: (fn_urlrev(l.path_base) = "outer".rev_path_base) (4 rows) 7.4: # explain select link_id from links l join clm_tmp_links t on (fn_urlrev(l.path_base) = t.rev_path_base); QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=5.50..88832.88 rows=1705551 width=4) Hash Cond: (fn_urlrev("outer".path_base) = "inner".rev_path_base) -> Seq Scan on links l (cost=0.00..50452.50 rows=1705550 width=78) -> Hash (cost=5.00..5.00 rows=200 width=74) -> Seq Scan on clm_tmp_links t (cost=0.00..5.00 rows=200 width=74) (5 rows) Although the cost for the 7.4 query is lower, the 7.3 plan executes in about 3 seconds, while the 7.4 plan executes in 59.8 seconds! Now the odd part: if I change the query to this: # explain analyze select link_id from links l join clm_tmp_links t on (fn_urlrev(l.path_base) = fn_urlrev(t.rev_path_base)); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=12.64..219974.16 rows=1705551 width=4) (actual time=17.928..17.928 rows=0 loops=1) Merge Cond: (fn_urlrev("outer".path_base) = "inner"."?column2?") -> Index Scan using ix_links_3 on links l (cost=0.00..173058.87 rows=1705550 width=78) (actual time=0.229..0.285 rows=7 loops=1) -> Sort (cost=12.64..13.14 rows=200 width=74) (actual time=9.652..9.871 rows=200 loops=1) Sort Key: fn_urlrev(t.rev_path_base) -> Seq Scan on clm_tmp_links t (cost=0.00..5.00 rows=200 width=74) (actual time=0.166..5.753 rows=200 loops=1) Total runtime: 18.125 ms (i.e., apply the function to the data in the temp table), it runs a whole lot faster! Is this a bug in the optimizer? Or did something change about the way functional indexes are used? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise