Re: appendum: Re: *really* simple select doesn't use indices ... - Mailing list pgsql-hackers

Marc,

The column 'zip' is of type text. As such, indices will not be used except
in the case when the where clause is WHERE zip ~ '^<text>' for btree
indices.

Gavin

On Tue, 29 May 2001, Marc G. Fournier wrote:

> 
> Okay, just bit the bullet, upgraded to v7.1.2, and the problem still
> persists:
> 
> globalmatch=# vacuum verbose analyze locations;
> NOTICE:  --Relation locations--
> NOTICE:  Pages 1395: Changed 0, reaped 0, Empty 0, New 0; Tup 123571: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen
76,MaxLen 124; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.11s/0.00u sec.
 
> NOTICE:  Index locationstmp_gid_key: Pages 272; Tuples 123571. CPU 0.01s/0.15u sec.
> NOTICE:  Index locations_zip: Pages 320; Tuples 123571. CPU 0.02s/0.14u sec.
> NOTICE:  Index locations_country: Pages 342; Tuples 123571. CPU 0.03s/0.13u sec.
> NOTICE:  --Relation pg_toast_9373225--
> NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0,
MaxLen0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
 
> NOTICE:  Index pg_toast_9373225_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
> NOTICE:  Analyzing...
> VACUUM
> globalmatch=# explain SELECT location from locations WHERE zip = '80012';
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on locations  (cost=0.00..2939.64 rows=4217 width=16)
> 
> EXPLAIN
> globalmatch=# select version();
>                                version
> ---------------------------------------------------------------------
>  PostgreSQL 7.1.2 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
> (1 row)
> 
> 
> On Mon, 28 May 2001, Marc G. Fournier wrote:
> 
> >
> > First, this is still a v7.1 system ... its totally possible that this is
> > long fixed, and I'm way overdue to get it to v7.1.2, which I'll gladly
> > accept as a response ...
> >
> > That said ... seems like a very painful way to arrive at 1 row ... :)
> >
> > table structure:
> >
> > globalmatch=# \d locations
> >                               Table "locations"
> >  Attribute |  Type   |                        Modifier
> > -----------+---------+--------------------------------------------------------
> >  gid       | integer | not null default nextval('locationstmp_gid_seq'::text)
> >  city      | text    |
> >  state     | text    |
> >  country   | text    |
> >  zip       | text    |
> >  location  | point   |
> > Indices: locations_zip,
> >          locationstmp_gid_key
> >
> > globalmatch=# \d locations_zip
> > Index "locations_zip"
> >  Attribute | Type
> > -----------+------
> >  zip       | text
> > btree
> >
> > globalmatch=# EXPLAIN SELECT count(location) from locations WHERE zip = '80012';
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=2950.18..2950.18 rows=1 width=16)
> >   ->  Seq Scan on locations  (cost=0.00..2939.64 rows=4217 width=16)
> >
> > EXPLAIN
> >
> > globalmatch=# SELECT count(location) from locations WHERE zip = '80012';
> >  count
> > -------
> >      1
> > (1 row)
> >
> > globalmatch=# SELECT count(location) from locations;
> >  count
> > --------
> >  123571
> > (1 row)
> >
> >
> >
> >
> 
> Marc G. Fournier                               scrappy@hub.org
> Systems Administrator @ hub.org
> scrappy@{postgresql|isc}.org                       ICQ#7615664
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>