From: | Felix Morley Finch <felix(at)crowfix(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Yet Another newbie not understanding why an index isn't used |
Date: | 1999-07-02 05:23:54 |
Message-ID: | 14204.19690.761826.751930@crowfix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a simple table of data from temperature probes, with two indices:
hlt=> \d hltdata
Table = hltdata
+------------------+------------------+-------+
| Field | Type | Length|
+------------------+------------------+-------+
| id | int2 | 2 |
| stmp | datetime | 8 |
| raw | int2 | 2 |
| cooked | int2 | 2 |
+------------------+------------------+-------+
hlt=> \d hltdata_all
+------------------+------------------+-------+
| id | int2 | 2 |
| stmp | datetime | 8 |
+------------------+------------------+-------+
(hltdata_all is a UNIQUE index to prevent dups.)
hlt=> \d hltdata_stmp
+------------------+------------------+-------+
| stmp | datetime | 8 |
+------------------+------------------+-------+
There are 1.5 million rows. It has been VACUUMed since the indices
were initially created in the COPY, and no rows have been added
since. It would seem like a perfect opportunity for the following
SELECT to use the indices; just grab the first 10 rows. Yet it grinds
for 93 seconds on a PII 450 w/ 256M of RAM. It's only 77 seconds if I
order by stmp alone.
hlt=> EXPLAIN SELECT * FROM hltdata ORDER BY stmp, id LIMIT 10;
NOTICE: QUERY PLAN:
Sort (cost=62394.19 rows=1584824 width=14)
-> Seq Scan on hltdata (cost=62394.19 rows=1584824 width=14)
The only entry in the FAQ prompted me to do the VACUUM, but I didn't
see anything else related to this. I reckond it must be pretty basic,
though...
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix(at)crowfix(dot)com
PGP = 91 B3 94 7C E9 E8 76 2D E1 63 51 AA A0 48 89 2F ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Bitmead | 1999-07-02 06:11:00 | Re: [GENERAL] Yet Another newbie not understanding why an index isn't used |
Previous Message | Travis | 1999-07-02 05:03:00 |