X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A21004763A2 for ; Mon, 5 May 2003 08:16:36 -0400 (EDT) Received: from gate-w.wettzell.ifag.de (gate-w.ifag.de [141.74.1.1]) by postgresql.org (Postfix) with ESMTP id 4BEA2476384 for ; Mon, 5 May 2003 08:16:35 -0400 (EDT) Received: by gate-w.wettzell.ifag.de (8.11.6p2/8.11.6) id h45CGcx13798 for pgsql-sql@postgresql.org; Mon, 5 May 2003 12:16:38 GMT Received: (from localhost) by gate-w.wettzell.ifag.de (MSCAN) id 2/gate-w.wettzell.ifag.de/smtp-gw/mscan; Mon May 5 12:16:38 2003 Message-ID: <3EB6561B.6070709@wettzell.ifag.de> Date: Mon, 05 May 2003 14:16:27 +0200 From: Reiner Dassing Organization: BKG-Wettzell User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-DE; rv:1.0.2) Gecko/20021120 Netscape/7.01 X-Accept-Language: de, en, zh MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Indices are not used by the optimizer Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-12.9 required=5.0 tests=BAYES_01,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/49 X-Sequence-Number: 13214 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 | | 43169098 | f | f | 6 | | \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