From: | Alvar Freude <alvar(at)a-blast(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | bytea, index and like operator |
Date: | 2003-12-03 20:34:54 |
Message-ID: | 1828220000.1070483694@gnarzelwicht.delirium-arts.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Vivek Khera | 2003-12-03 21:05:14 | Re: Upgrading from 7.2.3 to....?? |
Previous Message | Jan Wieck | 2003-12-03 20:28:31 | Re: postgresql locks the whole table! |