pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (11+ messages)  latest in thread

Message-ID: <3BD3BFE0.55FC3EC0@wettzell.ifag.de>
Permalink:  ../3BD3BFE0.55FC3EC0@wettzell.ifag.de/
Also on:    postgresql.org/message-id/3BD3BFE0.55FC3EC0@wettzell.ifag.de

 · 

reply

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