Re: INDEX suggestion needed

At 01:43 PM 12/11/02, Thomas Beutin wrote:

>Hi,
>
>i need some help or suggestions for performance increasing on my queries.
>My version: PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
>
>My table is:
>
>CREATE TABLE "stat_pages" (
> "visit" timestamp with time zone,
> "script_id" integer,
> "a_id" character(30),
> "p_id" character(30),
> "m_id" smallint,
> "s_id" smallint,
> "session_id" character(50),
> "action" character(20)
>);
>This table contains 343554 rows and i have the following index:
>CREATE INDEX "stat_pages_m_id_idx" on "stat_pages" using btree ( "m_id"
>"int2_ops" );
>
>i cannot create an index like this:
>CREATE INDEX "stat_pages_datum_idx" on "stat_pages" ( date ("visit") );
>The error is about the »iscachable« tag of the index function.
>
>The table is vacuumed full analyzed.
>
>My typical queries are like that:
>SELECT count(a_id) AS count
> FROM (
> SELECT DISTINCT a_id FROM stat_pages
> WHERE m_id = '35'
> AND visit >= '2002-09-01'
> AND visit <= '2002-09-30'
> ) AS foo;

Does this trigger use of index?
visit >= '2002-09-01'::timestamp AND visit <= '2002-09-30'::timestamp