Postgres Pro
Downloads
Home   >   mailing lists

Thread: like not using indexes in 7.3b5?

like not using indexes in 7.3b5?

From
Lars
Date:
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

Re: like not using indexes in 7.3b5?

From
Tom Lane
Date:
Lars <lhofhansl@yahoo.com> writes:
> Is that expected behavior in 7.3?

It is if you initdb'd in a locale other than "C".  Use pg_controldata
to check.

            regards, tom lane

Re: like not using indexes in 7.3b5?

From
"andrzej"
Date:
okk
U¿ytkownik "Tom Lane" <tgl@sss.pgh.pa.us> napisa³ w wiadomo¶ci
news:19233.1036681055@sss.pgh.pa.us...
> Lars <lhofhansl@yahoo.com> writes:
> > Is that expected behavior in 7.3?
>
> It is if you initdb'd in a locale other than "C".  Use pg_controldata
> to check.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org