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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
