Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group