Re: select max(field) from table much faster with a group by clause?

[Palle Girgensohn <girgen(at)pingpong(dot)net>]

--On torsdag, november 01, 2007 11.34.42 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 
wrote:

> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> > --On torsdag, november 01, 2007 11.06.57 -0400 Tom Lane
> > <tgl(at)sss(dot)pgh(dot)pa(dot)us>  wrote:
> > > Um, you did re-ANALYZE the table after changing the setting?
>
> > alter table login alter userid SET statistics 1000;
> > vacuum analyze login;
>
> Hm, that's the approved procedure all right.  But the plans didn't
> change at all?  Not even the estimated number of rows?

Estimated number of rows did change from ~400 to ~1900, but the timing was 
the same.

Seems that the problem is that it is using an index on "when". Removing 
that index (login_when_idx) changes the plan, and makes the query equally 
fast whether group by is there or not. I may need the index, though, in 
which one more index, on (userid, "when"), will fix the problem. I'd rather 
get rid of an index than creating another one.

Anyway, I think I have two suggestions for a solution that will work for 
me. I still think it is strange that the group by clause so radically 
changes the behaviour and the query time.

Cheers,
Palle

pp=# \d login
                 Table "public.login"
Column |           Type           |     Modifiers
--------+--------------------------+--------------------
userid | text                     |
kursid | integer                  |
when   | timestamp with time zone |
mode   | text                     | default 'pm'::text
Indexes:
   "login_kurs_user_idx" btree (kursid, userid)
   "login_userid_idx" btree (userid)
   "login_when_idx" btree ("when")
Foreign-key constraints:
   "pp_fk1" FOREIGN KEY (userid) REFERENCES person(userid) ON UPDATE 
CASCADE ON DELETE CASCADE
   "pp_fk2" FOREIGN KEY (kursid) REFERENCES course(id) ON UPDATE CASCADE 
ON DELETE CASCADE