Re: [PERFORM] not using index for select min(...)

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> For example, the following query is not possible to
> "workaround" in PostgreSQL:

> select teams_desc.team_id, team_name, team_code, notes,
> min(teams_tree.treeno) as lnode, max(teams_tree.treeno) as rnode,
> parent.team_id as parent_id, count(*)/2 as tlevel
> from teams_desc JOIN teams_tree USING (team_id)
> join teams_tree parent ON parent.treeno < teams_tree.treeno
> join teams_tree parents on parents.treeno < teams_tree.treeno
> WHERE parent.treeno = (SELECT max(p1.treeno) from teams_tree p1
> where p1.treeno < teams_tree.treeno
> and exists (select treeno from teams_tree p2
> where p2.treeno > teams_tree.treeno
> and p2.team_id = p1.team_id))
> AND EXISTS (select parents2.team_id from teams_tree parents2
> where parents2.treeno > teams_tree.treeno
> AND parents2.team_id = parents.team_id)
> group by teams_desc.team_id, team_name, team_code, notes, parent.team_id;

> While one would hardly expect the above query to be fast, it is dissapointing
> that it takes about 8-10 times as long to execute on PostgreSQL as on MSSQL,
> since MSSQL seems to be able to use indexes to evaluate all three MIN() and
> MAX() expressions.

I think you are leaping to conclusions about why there's a speed
difference. Or maybe I'm too dumb to see how an index could be used
to speed these min/max operations --- but I don't see that one would
be useful. Certainly not an index on treeno alone. Would you care to
explain exactly how it's done?

regards, tom lane