Skip site navigation (1) Skip section navigation (2)

bug: LC_CTYPE=en_US.UTF-8 confuses query planner

From: Dmitry Karasik <dk(at)catpipe(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bug: LC_CTYPE=en_US.UTF-8 confuses query planner
Date: 2005-06-28 08:37:38
Message-ID: 20050628083738.GA61677@tetsuo.karasik.eu.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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  

Responses

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2005-06-28 08:38:24
Subject: Re: commit_delay, siblings
Previous:From: Tatsuo IshiiDate: 2005-06-28 07:59:02
Subject: text search ysing ltree

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group