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

This is one my top two problem with Postgres, the seemingly braindead index
selection mechanism.

First, of course try "VACUUM ANALYZE'
Then if the fails, try

set ENABLE_SEQSCAN = off;

Then try your query.

"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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)