bytea, index and like operator

[Alvar Freude]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

the following I posted already on pgsql-bugs -- perhaps someone has a good
workaround or fix or can say me that I'm wrong?

There seems to be a bug in handling bytea columns with index and the
like-operator.

When an index scan on a bytea column is active, a query with "like" and "%"
in the search doesn't give the correct result: it finds always 0 rows.

At least in 7.3.4. When the FreeBSD Port for 7.4 is ready (yes, why isn't
it ready?), I'll test this again ... ;-)
Look the test here:
select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
begin;
create table test (b bytea);
create index tst_idx on test(b);
insert into test values ('\001abc\006');
insert into test values ('\001xabc\006');
insert into test values ('\001\002abc\006');
insert into test values ('\000\001\002abc\006');
insert into test values ('\002\003abc\006');

select * from test where b like '\001%';
Result:

b
---
(0 Zeilen) [0 rows]
explain analyze select * from test where b like '\001%';
QUERY PLAN
------------------------------------------------------------------------
Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32)
(actual time=0.04..0.04 rows=0 loops=1)
Index Cond: (b = '0'::bytea)
Filter: (b ~~ '\\001%'::bytea)
Total runtime: 0.14 msec
But with seq scan (after vacuuming, creating index later, dropping the
index...) it works as expected:

drop index tst_idx;
online_demo=> select * from test where b like '\001%';
b
-----------------
\001abc\006
\001xabc\006
\001\002abc\006
(3 Zeilen)
explain analyze select * from test where b like '\001%';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual
time=0.05..0.08 rows=3 loops=1)
Filter: (b ~~ '\\001%'::bytea)
Total runtime: 0.16 msec
hmmm ...

It seems, that bytea is no good idea for production use?
Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zkjvOndlH63J86wRAiNGAKCM/PQL1HxJj55WI0ZaUnk/wFazXgCggdIK
N1CiyG/+HtFT4lp4pZpfSD4=
=fa7q
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html