Обсуждение: 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