From: Reiner Dassing <dassing@wettzell.ifag.de>
To: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Index of a table is not used (in any case)
Date: Mon, 22 Oct 2001 08:42:40 +0200
Message-ID: <3BD3BFE0.55FC3EC0@wettzell.ifag.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dassing@wettzell.ifag.de, pgsql-hackers@postgresql.org
Subject: Re: Index of a table is not used (in any case)
In-Reply-To: <3BD3BFE0.55FC3EC0@wettzell.ifag.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox