Re: [HACKERS] PATCH: CITEXT 2.0 v2

[David E. Wheeler]

And here is the script. D'oh!

Thanks,

David

try.sql
Description: Binary data

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).

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 this leaves me with two questions:





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

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: 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? 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?)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers