X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EDBAB475E77 for ; Tue, 6 May 2003 10:00:45 -0400 (EDT) Received: from gate-w.wettzell.ifag.de (gate-w.ifag.de [141.74.1.1]) by postgresql.org (Postfix) with ESMTP id C689B474E42 for ; Tue, 6 May 2003 10:00:44 -0400 (EDT) Received: by gate-w.wettzell.ifag.de (8.11.6p2/8.11.6) id h46E0mg16565; Tue, 6 May 2003 14:00:48 GMT Received: (from localhost) by gate-w.wettzell.ifag.de (MSCAN) id 2/gate-w.wettzell.ifag.de/smtp-gw/mscan; Tue May 6 14:00:48 2003 Message-ID: <3EB7C000.9030206@wettzell.ifag.de> Date: Tue, 06 May 2003 16:00:32 +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: Richard Huxton Cc: pgsql-performance@postgresql.org Subject: Re: Select on timestamp-day slower than timestamp alone References: <3EB75D5F.5080406@wettzell.ifag.de> <200305061404.24194.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-22.6 required=5.0 tests=BAYES_01,QUOTED_EMAIL_TEXT,REFERENCES,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/65 X-Sequence-Number: 1844 Hello Richard! Your proposal to use an explicit cast to "with time zone" helps: explain select * from wetter where epoche between '2003-05-06 06:50:54+00'::timestamp with time zone-'1 days'::interval 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) The result now is like expected. Thanks for the help. But for your question "why we get "timestamp without time zone"." I have no answer. Reiner > >>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) > > > Well, the "why" is because the number of rows recommended is so big > (rows=10253528) - I'm also puzzled why we get "timestamp without time zone". > Does an explicit cast to "with time zone" help? >