I encountered a bug where the same query behaves differently
under different LC_CTYPE settings, "C" and "en_US.UTF-8".
The query is of type SELECT ... WHERE a like 'x' and b like 'y', where relevant
indexes exist for a and b, and 'x' and 'y' strings do not contain the %
character. When database is initdb'ed with LC_CTYPE=C, the query uses index
scan; when LC_CTYPE=en_US.UTF-8 it is the sequential scan. The table is large,
so it doesn't seem that planner selects seqscan out of performance reasons.
Also, I think this is a bug since when the query contains only one 'like'
statement, the query planner does use the index, no matter what $LC_CTYPE
value is.
Details:
pgsql 8.0.3
LC_CTYPE=C:
# explain select * from queues where username like 'a' and hostname like 'b';
QUERY PLAN
------------------------------------------------------------------------------------------
Index Scan using queues_idx_hostname_time on queues (cost=0.00..11.48 rows=1
width=161)
Index Cond: (hostname = 'b'::text)
Filter: ((username ~~ 'a'::text) AND (hostname ~~ 'b'::text))
(3 rows)
LC_CTYPE=en_US.UTF-8:
# explain select * from queues where username like 'a' and hostname like 'b';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on queues (cost=100000000.00..100000016.15 rows=1 width=161)
Filter: ((username ~~ 'a'::text) AND (hostname ~~ 'b'::text))
(2 rows)
# \d queues
...
username | text | not null
hostname | text | not null
--
Sincerely,
Dmitry Karasik
---
catpipe Systems ApS
*BSD solutions, consulting, development
www.catpipe.net
+45 7021 0050
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
