Re: INDEX suggestion needed

At 02:09 PM 12/11/02, Thomas Beutin wrote:

>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.


So add an index on "visit" - but forget trying to make it a 'date' index.
         CREATE INDEX "stat_pages_datum_idx" on "stat_pages" ( "visit" );
Instead of trying to make the index match the constants in your query, just
cast the constants in your query match the index.

On second glance, I've noticed something else.  I seem to remember seeing
question like this one before... and the suggested fix... create an index
on two fields (m_id, visit) and rewrite the query:

WHERE m_id >= '35' AND visit >= timestamp('2002-09-01') AND m_id <= '35'
AND visit <= timestamp('2002-09-30')

Frank