Re: appendum: Re: *really* simple select doesn't use indices ...

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, MaxLen 0; 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(at)hub(dot)org
> Systems Administrator @ hub.org
> scrappy(at){postgresql|isc}.org ICQ#7615664
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>