Csaba Nagy wrote:
[snip]
even is that is true i think you need the "comodin characters" (ie: %.
_) to make "like" behave different from simple comparisons
Not entirely true, if the database was initialized in a different locale
than C, then the direct comparison will probably go for an index on
username, while "like" will not. Which points to a possible index
corruption... which might be interesting for the developers to
investigate, but I would guess a reindex will solve the problem for the
OP if he has it urgent...
I thought that it may be a locale problem, but:
- look at my 3rd query
- potyty doesn't contain special chars
# EXPLAIN ANALYZE select * from common_logins where username = 'potyty';
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using common_logins_username_idx on common_logins
(cost=0.00..4.30 rows=1 width=47) (actual time=0.056..0.056 rows=0 loops=1)
Index Cond: ((username)::text = 'potyty'::text)
Total runtime: 0.109 ms
(3 rows)
online=# EXPLAIN ANALYZE select * from common_logins where username like
'potyty';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on common_logins (cost=0.00..63833.88 rows=1 width=47)
(actual time=180.333..262.492 rows=3 loops=1)
Filter: ((username)::text ~~ 'potyty'::text)
Total runtime: 262.551 ms
(3 rows)
I tried it in two databases (dump and load to another one), so I don't
think that we have corrupted indexes.
I can try on a newer version of postgresql on another server.
By the way, if this is a bug then it's a serious one. We have it in
production environment.
Mage
