Timestamp index not used

From: <david(at)stanaway(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Timestamp index not used
Date: 2003-05-08 03:56:15
Message-ID: 50240.10.0.50.1.1052366175.squirrel@david.stanaway.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-05-08 04:13:20 Re: [PERFORM] [SQL] Unanswered Questions WAS: An unresolved performance problem.
Previous Message Bryan Klimt 2003-05-08 02:23:17 insert rowtype