8.3 planner ignore index with text_pattern_ops for eq

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Pg Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: 8.3 planner ignore index with text_pattern_ops for eq
Date: 2008-08-06 14:55:16
Message-ID: 162867790808060755v75946dceo714cf7131eeddc54@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.27 rows=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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-08-06 15:03:29 Re: 8.3 planner ignore index with text_pattern_ops for eq
Previous Message Tom Lane 2008-08-06 14:42:52 Re: Status of DISTINCT-by-hashing work