Re: flagging first row inserted for each "group" of key

On Thu, 17 Dec 2009 10:38:32 +0100
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:

> In response to Ivan Sergio Borgonovo :
> > I've a web application.
> >
> > I'm logging data related to the landing page and subsequent
> > selected hits.
> >
> > create table track_hit (
> >   hitid serial not null,
> >   /* pk? I don't mind if serial wrap around
> >   pk could be (hitid, tracking_time) */
> >   esid varchar(32), -- related to session
> >   tracking_time timestamp not null default now(),
> >   -- some other stats
> >   -- first_hit boolean not null default false, -- ???
> > );
> >
> > I'd like to be sure I just count one "first hit" in a session
> > (same esid) in an efficient way that means I'd like to mark them
> > with a
>
> select distinct on (esid) esid, tracking_time from track_hit order
> by esid, tracking_time;
>
> returns only one record for each esid, ordered by tracking_time.
> Should work with 8.x, maybe sice 7.x (I'm not sure)

I think I've tried to resolve a concurrency problem in the wrong
place... still... what is the difference between:

select min(hitid) as h from track_hit group by esid;

and

select distinct on (esid) hitid from track_hit order by esid,
track_time;

I haven't enough test data to see if they perform differently. The
second form seems to perform a little bit faster.
I'd expect the opposite: the first performing better.

I think I'll add an index on track_time for reporting and maybe make
primary key (hitid, tracking_time).
I don't want to be bothered by hitid wrap around, so I don't want to
make it a pk alone, still I may need a pk.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it