Index of a table is not used (in any case)

From: Reiner Dassing <dassing(at)wettzell(dot)ifag(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Index of a table is not used (in any case)
Date: 2001-10-22 06:42:40
Message-ID: 3BD3BFE0.55FC3EC0@wettzell.ifag.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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 null
epoche | timestamp with time zone | not null
wert | 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 | integer
epoche | 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christof Petig 2001-10-22 07:43:07 HISTORY (ecpg enhancements not yet mentioned)
Previous Message Thomas Lockhart 2001-10-22 05:56:54 Re: [GENERAL] To Postgres Devs : Wouldn't changing the select limit

Browse pgsql-sql by date

  From Date Subject
Next Message Arian Prins 2001-10-22 07:30:16 Re: Auto Increment
Previous Message Mayuresh Kadu 2001-10-22 06:36:21 Auto Increment