No, *really* Sheesh, sorry.
David
try.sql
Description: Binary data
On Jul 7, 2008, at 16:26, David E. Wheeler wrote:
And here is the script. D'oh! Thanks, David <try.sql> On Jul 7, 2008, at 16:24, David E. Wheeler wrote:On Jul 7, 2008, at 08:01, Andrew Dunstan wrote:What does still bother me is its performance. I'd like to know if any measurement has been done of using citext vs. a functional index on lower(foo).Okay, here's a start. The attached script inserts random strings of 1-10 space-delimited words into text and citext columns, and then compares the performance of queries with and without indexes. The output for me is as follows:Loading words from dictionary. Inserting into the table. Test =. SELECT * FROM try WHERE LOWER(text) = LOWER('food'); Time: 254.254 ms SELECT * FROM try WHERE citext = 'food'; Time: 288.535 ms Test LIKE and ILIKE SELECT * FROM try WHERE LOWER(text) LIKE LOWER('C%'); Time: 209.385 ms SELECT * FROM try WHERE citext ILIKE 'C%'; Time: 236.186 ms SELECT * FROM try WHERE citext LIKE 'C%'; Time: 235.818 ms Adding indexes... Test =. SELECT * FROM try WHERE LOWER(text) = LOWER('food'); Time: 1.260 ms SELECT * FROM try WHERE citext = 'food'; Time: 277.755 ms Test LIKE and ILIKE SELECT * FROM try WHERE LOWER(text) LIKE LOWER('C%'); Time: 209.073 ms SELECT * FROM try WHERE citext ILIKE 'C%'; Time: 238.430 ms SELECT * FROM try WHERE citext LIKE 'C%'; Time: 238.685 ms benedict%So for some reason, after adding the indexes, the queries against the CITEXT column aren't using them. Furthermore, the `lower(text) LIKE lower(?)` query isn't using *its* index. Huh?So this leaves me with two questions:1. For what reason would the query against the citext column *not* use the index?2. Is there some way to get the CITEXT index to behave like a LOWER() index, that is, so that its value is stored using the result of the str_tolower() function, thus removing some of the overhead of converting the values for each row fetched from the index? (Does this question make any sense?)Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
