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';
QUERYPLAN
------------------------------------------------------------------------------------------Index Scan using
queues_idx_hostname_timeon 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.15rows=1 width=161) Filter: ((username ~~ 'a'::text) AND (hostname ~~ 'b'::text))
(2 rows)
# \d queues...username | text | not nullhostname | text | not null
--
Sincerely,Dmitry Karasik
---
catpipe Systems ApS
*BSD solutions, consulting, development
www.catpipe.net
+45 7021 0050
