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