Hello
there is some different behave between 8.3 and 8.1, 8.4 versions. 8.3
can't share index for like and equ op.
postgres=# select version(); version
----------------------------------------------------------------------------------------------------PostgreSQL 8.3.0 on
i686-pc-linux-gnu,compiled by GCC gcc (GCC)
4.1.2 20070925 (Red Hat 4.1.2-33)
(1 row)
postgres=# \i ~/Desktop/pagila-0.10.1/pagila-schema.sql
postgres=# \i ~/Desktop/pagila-0.10.1/pagila-data.sql
postgres=# CREATE index city_idx1 on city (city varchar_pattern_ops);
CREATE INDEX
postgres=# ANALYZE city
postgres-# ;
ANALYZE
postgres=# explain select * from city where city = 'Prague'; QUERY PLAN
------------------------------------------------------Seq Scan on city (cost=0.00..11.50 rows=1 width=23) Filter:
((city)::text= 'Prague'::text)
(2 rows)
postgres=# explain select * from city where city like 'Prague'; QUERY PLAN
-----------------------------------------------------------------------Index Scan using city_idx1 on city
(cost=0.00..8.27rows=1 width=23) Index Cond: ((city)::text ~=~ 'Prague'::text) Filter: ((city)::text ~~
'Prague'::text)
(3 rows)
postgres=# explain select * from city where city like 'Prague%'; QUERY PLAN
------------------------------------------------------------------------------------------Index Scan using city_idx1 on
city (cost=0.00..8.27 rows=1 width=23) Index Cond: (((city)::text ~>=~ 'Prague'::text) AND ((city)::text
~<~ 'Praguf'::text)) Filter: ((city)::text ~~ 'Prague%'::text)
(3 rows)
8.4 works correct - is it possible backport?
Regards
Pavel Stehule
