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

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