From: Reiner Dassing <dassing@wettzell.ifag.de>
To: pgsql-sql@postgresql.org
Subject: Indices are not used by the optimizer
Date: Mon, 05 May 2003 14:16:27 +0200
Message-ID: <3EB6561B.6070709@wettzell.ifag.de> (raw)
Hello all!
On PostgreSQL V7.3.2 on TRU64 I have a table
and applied indices for that table.
But on a simple query the indices are not used by the optimizer.
(An sequential scan is used which takes a lot of time)
I have done
VACUUM and VACUUM analyze
but without any change to the optimizer.
Can someone give me a hint what I should do to give the
optimizer a start?
--------------------------------------
Well, let's start by the query
wetter=# explain select * from wetter where epoche > '2001-01-01';
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on wetter (cost=0.00..614795.55 rows=19054156 width=16)
Filter: (epoche > '2001-01-01 00:00:00+00'::timestamp with time zone)
(2 rows)
wetter=#
The table definition is as follows:
\d wetter
Table "public.wetter"
Column | Type | Modifiers
-----------+--------------------------+-----------
sensor_id | integer | not null
epoche | timestamp with time zone | not null
wert | real | not null
Indexes: wetter_pkey primary key btree (sensor_id, epoche),
wetter_epoche_idx btree (epoche),
wetter_sensor_id_idx btree (sensor_id)
Triggers: RI_ConstraintTrigger_45702811,
t_ins_wetter_wetterakt
wetter=#
The trigger information is as follows:
select * from pg_trigger where tgname='RI_ConstraintTrigger_45702811';
tgrelid | tgname | tgfoid | tgtype | tgenabled
| tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
----------+-------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------------------------------------------------------------------------------
43169106 | RI_ConstraintTrigger_45702811 | 1644 | 21 | t
| t | <unnamed> | 43169098 | f | f
| 6 | |
<unnamed>\000wetter\000sensoren_an_orten\000UNSPECIFIED\000sensor_id\000sensor_id\000
(1 row)
wetter=#
and t_ins_wetter_wetterakt
is a PLPGSQL Funktion which copies some information into another table
when an insert or update is done.
--
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
Subject: Re: Indices are not used by the optimizer
In-Reply-To: <3EB6561B.6070709@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