Thread: Yet Another newbie not understanding why an index isn't used
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@crowfix.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
Are you using btree indexes? It of course won't work for hash. Felix Morley Finch wrote: > > 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@crowfix.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
Re: [GENERAL] Yet Another newbie not understanding why an index isn't used
From
"Michal Maru¹ka"
Date:
> hlt=> EXPLAIN SELECT * FROM hltdata ORDER BY stmp, id LIMIT 10; If I remember well, the order of the index attributes must be the same as ... in the query which should use it: try "id, stmp"