Thread: BYTEA, indexes and "like"

Hi,

it seems, that a BYTEA fiels doesn't support indexes in WHERE-Statement
with a "like" condition:


test=# EXPLAIN ANALYZE SELECT COUNT(*) FROM bytea_test
       WHERE bytea_field like '\\000\\000\\001%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=668.72..668.72 rows=1 width=0) (actual time=16.63..16.64
rows=1 loops=1)
  ->  Seq Scan on bytea_test  (cost=0.00..668.71 rows=1 width=0) (actual
time=6.65..15.69 rows=145 loops=1)
Total runtime: 16.77 msec


But an equal condition uses indexes:

test=# EXPLAIN ANALYZE SELECT COUNT(*) FROM bytea_test
       WHERE bytea_field = '\\000\\000\\001';
NOTICE:  QUERY PLAN:

Aggregate  (cost=5.93..5.93 rows=1 width=0) (actual time=0.10..0.10
rows=1 loops=1)
  ->  Index Scan using bytea_test_bytea_field_idx on bytea_test
(cost=0.00..5.93 rows=1 width=0) (actual time=0.05..0.07 rows=1 loops=1)
Total runtime: 0.24 msec



With text fields instead of bytea indexes are used also with like, but
there are no nullbytes allowed and they are sorted using locales (if not
switched of).


Are there plans to allow also the "like" conditions on bytea indexes?


Thnx and Ciao
  Alvar

--
** ODEM ist für den poldi Award nominiert! http://www.poldiaward.de/
** http://www.poldiaward.de/index.php?display=detail&cat=audi&item=24
** http://odem.org/
** Mehr Projekte: http://alvar.a-blast.org/

Alvar Freude <alvar@a-blast.org> writes:
> it seems, that a BYTEA fiels doesn't support indexes in WHERE-Statement
> with a "like" condition:

I was somewhat bemused to discover that we even *have* a LIKE for bytea.
It does not seem to work real well:

regression=# create table foo (f1 bytea);
CREATE TABLE
regression=# select * from foo where f1 like 'z';
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The server log shows
TRAP: FailedAssertion("!(((Const *) other)->consttype == 25)", File: "selfuncs.c", Line: 857)
LOG:  server process (pid 8487) was terminated by signal 6

which traces down to the fact that patternsel() expects to see only TEXT
patterns.  Joe, was it you that put this stuff in?  If so, shame on you
for not using --enable-cassert when doing backend work.

            regards, tom lane

Tom Lane wrote:
> which traces down to the fact that patternsel() expects to see only TEXT
> patterns.  Joe, was it you that put this stuff in?  If so, shame on you
> for not using --enable-cassert when doing backend work.
>

Shame already acknowledged -- you must have written this before reading
my last post. I know better now. Anyway, patch was sent in to patches.

A new problem exists though. Again see my post to patches. Indexed
lookups on bytea are completely broken on cvs tip. On 7.2.1:

test=# explain select * from bombytea where parent_part = 'FM04-13100-1';
NOTICE:  QUERY PLAN:

Index Scan using bombytea_idx1 on bombytea  (cost=0.00..148.37 rows=37
width=34)

EXPLAIN
test=# select * from bombytea where parent_part = 'FM04-13100-1';
  parent_part  | child_part | child_part_qty
--------------+------------+----------------
  FM04-13100-1 | NULL       |              0
(1 row)

and on cvs tip:

parts=# explain select * from bombytea where parent_part = 'FM04-13100-1';
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Index Scan using bombytea_idx1 on bombytea  (cost=0.00..147.83 rows=37
width=34)
    Index Cond: (parent_part = 'FM04-13100-1'::bytea)
(2 rows)

parts=# select * from bombytea where parent_part = 'FM04-13100-1';
ERROR:  Index bombytea_idx1 is not a btree

I'm trying to figure out why right now.

Joe

Joe Conway <mail@joeconway.com> writes:
> parts=# select * from bombytea where parent_part = 'FM04-13100-1';
> ERROR:  Index bombytea_idx1 is not a btree

> I'm trying to figure out why right now.

FWIW, I'm not seeing that here... perhaps a clean rebuild is indicated?

            regards, tom lane