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