like not using indexes in 7.3b5?

From: Lars <lhofhansl(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: like not using indexes in 7.3b5?
Date: 2002-11-07 08:25:23
Message-ID: 3DCA2373.7070005@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have a table with a bunch of varchar columns.
In 7.2.3 Postgres would indexes on varchar column
for certain like queries.

This is a script from 7.2.3:
----------------------------
lars=> \d agency
Table "agency"
Column | Type | Modifiers
---------+-----------------------+-----------
nr | integer |
name1 | character varying(20) |
name2 | character varying(20) |
street | character varying(20) |
city | character varying(10) |
zip | character varying(10) |
phone | character varying(15) |
blocked | boolean |
Indexes: agency_zip

lars=> \d agency_zip
Index "agency_zip"
Column | Type
--------+-----------------------
zip | character varying(10)
btree

lars=> explain select * from agency where zip = 'abc';
NOTICE: QUERY PLAN:

Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90)

EXPLAIN
lars=> explain select * from agency where zip like 'abc';
NOTICE: QUERY PLAN:

Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90)

EXPLAIN
lars=> explain select * from agency where zip like 'abc%';
NOTICE: QUERY PLAN:

Index Scan using agency_zip on agency (cost=0.00..3.01 rows=1 width=90)

EXPLAIN

The same table 7.3b5 results in this:
-------------------------------------
lars=> explain select * from agency where zip = 'abc';
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using agency_zip on agency (cost=0.00..393.64 rows=112
width=124)
Index Cond: (zip = 'abc'::character varying)
(2 rows)

lars=> explain select * from agency where zip like 'abc';
QUERY PLAN
------------------------------------------------------------
Seq Scan on agency (cost=0.00..653.50 rows=112 width=124)
Filter: (zip ~~ 'abc'::text)
(2 rows)

lars=> explain select * from agency where zip like 'abc%';
QUERY PLAN
------------------------------------------------------------
Seq Scan on agency (cost=0.00..653.50 rows=112 width=124)
Filter: (zip ~~ 'abc%'::text)
(2 rows)

i.e. is correctly uses the index for the = operator but not for like
(if used as prefix operator)

In both cases the tables are "analyzed" and the like queries execute
slower in 7.3b5.

Is that expected behavior in 7.3?

-- Lars

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2002-11-07 09:04:28 Bug #811: Using || with char and char varying
Previous Message Peter Eisentraut 2002-11-06 23:30:00 Re: hi-problem in creating database in postgresql 7.2.2