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


---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to