--On torsdag, november 01, 2007 11.34.42 -0400 Tom Lane <tgl@xxxxxxxxxxxxx>
wrote:
Palle Girgensohn <girgen@xxxxxxxxxxxx> writes:
--On torsdag, november 01, 2007 11.06.57 -0400 Tom Lane
<tgl@xxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
