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@sss.pgh.pa.us>
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> --On torsdag, november 01, 2007 11.06.57 -0400 Tom Lane
>> <tgl@sss.pgh.pa.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