X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2454E4764B8 for ; Tue, 6 May 2003 02:59:53 -0400 (EDT) Received: from gate-w.wettzell.ifag.de (gate-w.ifag.de [141.74.1.1]) by postgresql.org (Postfix) with ESMTP id 2BBEB4764AD for ; Tue, 6 May 2003 02:59:50 -0400 (EDT) Received: by gate-w.wettzell.ifag.de (8.11.6p2/8.11.6) id h466xo320845 for pgsql-performance@postgresql.org; Tue, 6 May 2003 06:59:50 GMT Received: (from localhost) by gate-w.wettzell.ifag.de (MSCAN) id 2/gate-w.wettzell.ifag.de/smtp-gw/mscan; Tue May 6 06:59:50 2003 Message-ID: <3EB75D5F.5080406@wettzell.ifag.de> Date: Tue, 06 May 2003 08:59:43 +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-performance@postgresql.org Subject: Select on timestamp-day slower than timestamp alone 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/56 X-Sequence-Number: 1835 Hello all! On PostgreSQL V7.3.2 on TRU64 I recognized the following phenomena that a SELECT using a difference of a timestamp and an interval in the WHERE clause does not use the index but using a timestamp without a difference does use the index. The semantic of both SELECT's is equal, i.e., the result is equal. Therefore, the second way is much faster. Any ideas? In detail: table: wetter=# \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 Select not using index: ----------------------- wetter=# explain select * from wetter where epoche between '2003-05-06 06:50:54+00'::timestamp-'1 days'::interval AND '2003-05-06 04:45:36'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on wetter (cost=0.00..768644.57 rows=10253528 width=16) Filter: ((epoche >= ('2003-05-05 06:50:54'::timestamp without time zone)::timestamp with time zone) AND (epoche <= '2003-05-06 04:45:36+00'::timestamp with time zone)) (2 rows) wetter=# Select using the index: ----------------------- explain select * from wetter where epoche between '2003-05-05 06:50:54' AND '2003-05-06 04:45:36'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using wetter_epoche_idx on wetter (cost=0.00..5.45 rows=1 width=16) Index Cond: ((epoche >= '2003-05-05 06:50:54+00'::timestamp with time zone) AND (epoche <= '2003-05-06 04:45:36+00'::timestamp with time zone)) (2 rows) wetter=# -- Mit freundlichen Gruessen / With best regards Reiner Dassing