X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CD254475F0D for ; Thu, 8 May 2003 00:43:44 -0400 (EDT) Received: from ciderbox.stanaway.net (ciderbox.stanaway.net [170.76.50.98]) by postgresql.org (Postfix) with ESMTP id C43A6475B8E for ; Thu, 8 May 2003 00:43:42 -0400 (EDT) Received: from stanaway.net (localhost [127.0.0.1]) by ciderbox.stanaway.net (Postfix) with SMTP id 122E41B3F for ; Wed, 7 May 2003 22:56:15 -0500 (CDT) Received: from 10.0.50.1 (SquirrelMail authenticated user david) by david.stanaway.net with HTTP; Wed, 7 May 2003 22:56:15 -0500 (CDT) Message-ID: <50240.10.0.50.1.1052366175.squirrel@david.stanaway.net> Date: Wed, 7 May 2003 22:56:15 -0500 (CDT) Subject: Timestamp index not used From: To: X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal X-Mailer: SquirrelMail (version 1.2.6) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-4.7 required=5.0 tests=BAYES_10,MISSING_MIMEOLE,MISSING_OUTLOOK_NAME,NO_REAL_NAME version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/154 X-Sequence-Number: 13319 What kind of index should I be using on the timestamp column for it to be used for BETWEEN and > operations? radius=3D# EXPLAIN SELECT * from alldata where loggedat > '2003-05-07'; NOTICE: QUERY PLAN: Seq Scan on alldata (cost=3D0.00..286136.26 rows=3D1428487 width=3D780) EXPLAIN radius=3D# \d alldata Table "alldata" Column | Type | Modifiers -----------------------+--------------------------+------------------------= ---------------------------- query | text | cisco_nas_port | text | h323_remote_address | text | h323_conf_id | text | h323_setup_time | text | h323_call_origin | text | h323_call_type | text | h323_connect_time | text | h323_disconnect_time | text | h323_disconnect_cause | text | h323_voice_quality | text | h323_gw_id | text | nas_ip_address | text | nas_port_type | text | called_station_id | text | calling_station_id | text | acct_status_type | text | acct_session_id | text | acct_input_octets | text | acct_output_octets | text | acct_input_packets | text | acct_output_packets | text | acct_session_time | text | acct_delay_time | text | identity | integer | default nextval('alldata_identity_seq'::text) loggedat | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone Indexes: alldata_call_origin_idx, alldata_call_type_idx, alldata_conf_id_idx, alldata_nas_ip_address_idx, alldate_loggedat_idx, remote_address_idx Unique keys: alldata_identity_pkey radius=3D# \d alldate_loggedat_idx Index "alldate_loggedat_idx" Column | Type ----------+-------------------------- loggedat | timestamp with time zone btree