bytea, index and like operator - Mailing list pgsql-general

From Alvar Freude
Subject bytea, index and like operator
Date
Msg-id 1828220000.1070483694@gnarzelwicht.delirium-arts.de
Whole thread Raw
List pgsql-general
-----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-----


pgsql-general by date:

Previous
From: "Keith C. Perry"
Date:
Subject: Re: Feature Request for 7.5
Next
From: Vivek Khera
Date:
Subject: Re: Upgrading from 7.2.3 to....??