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/