Performance for case-insensitive queries

From: "Vince DiCiero" <vdiciero(at)home(dot)com>
To: "Postgresql General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Performance for case-insensitive queries
Date: 1999-11-01 06:32:08
Message-ID: 000501bf2432$d325d560$3600a8c0@home.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message amy cheng 1999-11-01 11:00:17 Re: [GENERAL] Auto Ordering
Previous Message Jan Vicherek 1999-11-01 04:56:13 userspace fs implementation ? (perhaps with PostgreSQL)