From: mlw <markw@mohawksoft.com>
To: Marc G. Fournier <scrappy@hub.org>
Cc: pgsql-hackers@postgresql.org
Subject: Re: *really* simple select doesn't use indices ...
Date: Tue, 29 May 2001 10:45:54 -0400
Message-ID: <3B13B622.41D25E5@mohawksoft.com> (raw)
References: <Pine.BSF.4.33.0105282334270.12563-100000@hub.org>
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@postgresql.org)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: markw@mohawksoft.com, scrappy@hub.org
Subject: Re: *really* simple select doesn't use indices ...
In-Reply-To: <3B13B622.41D25E5@mohawksoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox