Re: INDEX suggestion needed

On Wed, Dec 11, 2002 at 01:58:59PM -0500, Frank Bax wrote:
> 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

no, does not :-( but there is no index on visit.

-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.