bytea, index and like operator

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

Hi,

there is a bug in handling bytea columns with index and the like-operator.
At least in 7.3.4. When the FreeBSD Port for 7.4 is ready, I'll test this
... ;-)

When an index scan is active, a query dosn't give the correct result:

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
- ---------------------------------------------------------------------------
- -------------------
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
(3 Zeilen)

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, ...) 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/zhtwOndlH63J86wRAh9VAJ9tjx/MrvbMPjlqhQqvhbXLaIG5owCfRbAn
S65xELFQ6I9ObdzAXOTjIWM=
=7DuO
-----END PGP SIGNATURE-----