Index of a table is not used (in any case) - Mailing list pgsql-sql

Hello PostgreSQl Users!

PostSQL V 7.1.1:

I have defined a table and the necessary indices.
But the index is not used in every SELECT. (Therefore, the selects are
*very* slow, due to seq scan on
20 million entries, which is a test setup up to now)

The definitions can be seen in the annex.

Does some body know the reason and how to circumvent the seq scan?

Is the order of index creation relevant? I.e., should I create the
indices before inserting
entries or the other way around?

Should a hashing index be used? (I tried this, but I got the known error
"Out of overflow pages")
(The docu on "create index" says :    "Notes 
   The Postgres query optimizer will consider using a btree index
whenever an indexed attribute is involved in a   comparison using one of: <, <=, =, >=, > 
   The Postgres query optimizer will consider using an rtree index
whenever an indexed attribute is involved in a   comparison using one of: <<, &<, &>, >>, @, ~=, && 
   The Postgres query optimizer will consider using a hash index
whenever an indexed attribute is involved in a   comparison using the = operator. "


The table entry 'epoche' is used in two different indices. Should that
be avoided?

Any suggestions are welcome.

Thank you in advance.
Reiner
------------------------------
Annex:
======

Table:
------
\d wetter                Table "wetter"Attribute |           Type           | Modifier 
-----------+--------------------------+----------sensor_id | integer                  | not nullepoche    | timestamp
withtime zone | not nullwert      | real                     | not null
 
Indices: wetter_epoche_idx,        wetter_pkey
\d wetter_epoche_idx     Index "wetter_epoche_idx"Attribute |           Type           
-----------+--------------------------epoche    | timestamp with time zone
btree


\d wetter_pkey        Index "wetter_pkey"Attribute |           Type           
-----------+--------------------------sensor_id | integerepoche    | timestamp with time zone
unique btree (primary key)


Select where index is used:
============================
explain select * from wetter order by epoche desc;    
NOTICE:  QUERY PLAN:

Index Scan Backward using wetter_epoche_idx on wetter 
(cost=0.00..3216018.59 rows=20340000 width=16)

EXPLAIN



Select where the index is NOT used:
===================================
explain select * from wetter where epoche between '1970-01-01' and
'1980-01-01' order by epoche asc;
NOTICE:  QUERY PLAN:

Sort  (cost=480705.74..480705.74 rows=203400 width=16) ->  Seq Scan on wetter  (cost=0.00..454852.00 rows=203400
width=16)

EXPLAIN

--
Mit freundlichen Gruessen / With best regards  Reiner Dassing