Hello, I am in need of help with performance of case insensitive queries.
I have searched the general and hackers archives for references to this
problem and
have found the information on how to build a lower case index.
I have included output from 3 query plans that detail my performance
problem.
I show the section of the where clause that changes for the 3 queries.
The 1st shows the case sensitive search that I have used on other db's that
are
not case sensitive. As you can see it executes very quickly on postgresql.
The 2nd shows the original attempt at doing a case insensitive search before
building the lower case index, by using the lower() function in the query.
This caused the queries to become very slow.
In the 3rd example I built a lower case index and it cut the query time in
half, but the queries still run very long, near 2 minutes with approximately
1 million rows.
(1) Build index on fieldvalue column.
testdb=> create index fieldvalueindex_2 on documentindex(fieldvalue);
[case sensitive search]
testdb-> ((DocumentIndex_0.FieldNum = 2 AND documentindex_0.fieldvalue =
'BELL SOUTH') )
NOTICE: QUERY PLAN:
Unique (cost=6.06 rows=13 width=62)
-> Sort (cost=6.06 rows=13 width=62)
-> Nested Loop (cost=6.06 rows=13 width=62)
-> Nested Loop (cost=4.10 rows=1 width=46)
-> Index Scan using fieldvalueindex_2 on documentindex
documentindex_0 (cost=2.05 rows=)
-> Index Scan using documents_pkey on documents
(cost=2.05 rows=11432 width=42)
-> Seq Scan on documentmaster (cost=1.96 rows=29 width=16)
EXPLAIN
(2).
[case insensitive search with no lowercase index]
testdb-> ((DocumentIndex_0.FieldNum = 2 AND
lower(documentindex_0.fieldvalue) = 'bell south') )
NOTICE: QUERY PLAN:
Unique (cost=7579.73 rows=13 width=62)
-> Sort (cost=7579.73 rows=13 width=62)
-> Nested Loop (cost=7579.73 rows=13 width=62)
-> Nested Loop (cost=7577.77 rows=1 width=46)
-> Seq Scan on documentindex documentindex_0
(cost=7575.72 rows=1 width=4)
-> Index Scan using documents_pkey on documents
(cost=2.05 rows=11432 width=42)
-> Seq Scan on documentmaster (cost=1.96 rows=29 width=16)
EXPLAIN
(3). build lower case index.
testdb=> create index fieldvalueindex on documentindex(lower(fieldvalue)
text_ops);
[case insensitive search with lowercase index]
testdb-> ((DocumentIndex_0.FieldNum = 2 AND
lower(DocumentIndex_0.FieldValue) = 'bell south') )
NOTICE: QUERY PLAN:
Unique (cost=3820.81 rows=13 width=62)
-> Sort (cost=3820.81 rows=13 width=62)
-> Nested Loop (cost=3820.81 rows=13 width=62)
-> Nested Loop (cost=3818.85 rows=1 width=46)
-> Index Scan using fieldvalueindex on documentindex
documentindex_0 (cost=3816.80 rows)
-> Index Scan using documents_pkey on documents
(cost=2.05 rows=11432 width=42)
-> Seq Scan on documentmaster (cost=1.96 rows=29 width=16)
EXPLAIN