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(at)laokoon(dot)IN-Berlin(dot)DE
Beam me up, Scotty. There is no intelligent live down in Redmond.