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
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 |