Обсуждение: 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

> 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"