db=> \i /tmp/sql-bug.txt ROLLBACK BEGIN CREATE TABLE CREATE INDEX INSERT 184807 1 INSERT 184808 1 INSERT 184809 1 INSERT 184810 1 INSERT 184811 1 INSERT 184812 1 INSERT 184813 1 INSERT 184814 1 INSERT 184815 1 INSERT 184816 1 INSERT 184817 1 INSERT 184818 1 INSERT 184819 1 INSERT 184820 1 INSERT 184821 1 SET SET bcol ------ (0 Zeilen) bcol ------ (0 Zeilen) bcol --------- abc abc\002 abc\003 abc\004 abc\005 abcdef (6 Zeilen) bcol --------- abc abc\002 abc\003 abc\004 abc\005 abcdef (6 Zeilen) bcol ------ (0 Zeilen) bcol ------ (0 Zeilen) bcol ------ (0 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.047..0.047 rows=0 loops=1) Index Cond: (bcol = '0'::bytea) Filter: (bcol ~~ '\\001%'::bytea) Total runtime: 0.151 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.048..0.048 rows=0 loops=1) Index Cond: (bcol = '00'::bytea) Filter: (bcol ~~ '\\001\\002%'::bytea) Total runtime: 0.151 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.079..0.134 rows=6 loops=1) Index Cond: ((bcol >= 'a'::bytea) AND (bcol < 'b'::bytea)) Filter: (bcol ~~ 'a%'::bytea) Total runtime: 0.275 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.077..0.152 rows=6 loops=1) Index Cond: ((bcol >= 'a'::bytea) AND (bcol < 'b'::bytea)) Filter: (bcol ~~ 'a%'::bytea) Total runtime: 0.535 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.047..0.047 rows=0 loops=1) Index Cond: (bcol = '3'::bytea) Filter: (bcol ~~ '\\344%'::bytea) Total runtime: 0.149 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.046..0.046 rows=0 loops=1) Index Cond: (bcol = '3'::bytea) Filter: (bcol ~~ '\\344%'::bytea) Total runtime: 0.147 ms (4 Zeilen) QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32) (actual time=0.048..0.048 rows=0 loops=1) Index Cond: (bcol = '0'::bytea) Filter: (bcol ~~ '\\001%'::bytea) Total runtime: 0.150 ms (4 Zeilen) SET SET bcol ----------------- \001abc\006 \001xabc\006 \001\002abc\006 \001abc\006 \001xabc\006 \001\002abc\006 (6 Zeilen) bcol ----------------- \001\002abc\006 \001\002abc\006 (2 Zeilen) bcol --------- abc abcdef abc\003 abc\002 abc\004 abc\005 (6 Zeilen) bcol --------- abc abcdef abc\003 abc\002 abc\004 abc\005 (6 Zeilen) bcol ------ (0 Zeilen) bcol ------ (0 Zeilen) bcol ----------------- \001abc\006 \001xabc\006 \001\002abc\006 \001abc\006 \001xabc\006 \001\002abc\006 (6 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.038..0.085 rows=6 loops=1) Filter: (bcol ~~ '\\001%'::bytea) Total runtime: 0.182 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.043..0.065 rows=2 loops=1) Filter: (bcol ~~ '\\001\\002%'::bytea) Total runtime: 0.141 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.038..0.096 rows=6 loops=1) Filter: (bcol ~~ 'a%'::bytea) Total runtime: 0.201 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.042..0.100 rows=6 loops=1) Filter: (bcol ~~ 'a%'::bytea) Total runtime: 0.211 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.051..0.051 rows=0 loops=1) Filter: (bcol ~~ '\\344%'::bytea) Total runtime: 0.115 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.051..0.051 rows=0 loops=1) Filter: (bcol ~~ '\\344%'::bytea) Total runtime: 0.113 ms (3 Zeilen) QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual time=0.039..0.087 rows=6 loops=1) Filter: (bcol ~~ '\\001%'::bytea) Total runtime: 0.187 ms (3 Zeilen)