Thread: Timestamp index not used

What kind of index should I be using on the timestamp column for it to

be used for BETWEEN and > operations?



radius=# EXPLAIN SELECT * from alldata where loggedat > '2003-05-07';

NOTICE:  QUERY PLAN:



Seq Scan on alldata  (cost=0.00..286136.26 rows=1428487 width=780)



EXPLAIN

radius=# \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=# \d alldate_loggedat_idx
   Index "alldate_loggedat_idx"
 Column  |           Type

----------+--------------------------
loggedat | timestamp with time zone

btree

<david@stanaway.net> writes:
> radius=# EXPLAIN SELECT * from alldata where loggedat > '2003-05-07';
> NOTICE:  QUERY PLAN:
> Seq Scan on alldata  (cost=0.00..286136.26 rows=1428487 width=780)
^^^^^^^^^^^^

The planner seems to think that this query will return rather a lot of
rows ... which would imply that a seqscan isn't necessarily a bad idea.
Is that rows estimate accurate?  (If not, have you ANALYZEd this table
lately?)
        regards, tom lane