Josh Berkus <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to