Yet Another newbie not understanding why an index isn't used

Поиск
Список
Период
Сортировка
От Felix Morley Finch
Тема Yet Another newbie not understanding why an index isn't used
Дата
Msg-id 14204.19690.761826.751930@crowfix.com
обсуждение исходный текст
Ответы Re: [GENERAL] Yet Another newbie not understanding why an index isn't used  ("Michal Maru¹ka" <mmaruska@tin.it>)
Список 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@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

В списке pgsql-general по дате отправления:

Предыдущее
От: Travis
Дата:
Сообщение: ...
Следующее
От: Chris Bitmead
Дата:
Сообщение: Re: [GENERAL] Yet Another newbie not understanding why an index isn't used