Grouping Too Closely

I have a table that looks like this:

CREATE TABLE my_table (
pkey serial PRIMARY KEY,
fkey int NOT NULL REFERENCES my_other_table( pkey ),
uid int NOT NULL REFERENCES user( pkey ),
seq1 int,
seq2 int
);

Basically, for each fkey that exists in my_table, there is a sequence
represented by seq1, which covers every record corresponding to a
given fkey. Then there is a subset of records covered by seq2, which
increments over the course of a given fkey, but might span multiple
records.

E.g.,

pkey | fkey | uid | seq1 | seq2
---------------------------------------
1 | 1 | 1 | 1 | 1
2 | 1 | 2 | 2 | 1
...

What I'd like to be able to do is select all records corresponding to
the minimum value of seq1 for each value of seq2 corresponding to a
given fkey (with a lower bound on the value of seq2).

My first attempt looked like this:

SELECT fkey, uid, seq2
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, seq2, uid, seq1
HAVING seq1 = min( seq1 )

but this groups too closely to return the desired results.

My next attempt looked like this (where I use the shorthand for min
in the subquery):

SELECT fkey, uid, seq2
FROM my_table AS mt1
WHERE mt1.seq2 > 2
AND ( mt1.uid, hh1.seq1 ) IN (
SELECT mt2.player_id, mt2.order_no
FROM my_table AS mt2
WHERE mt2.fkey = mt1.fkey
AND mt2.seq2 = mt1.seq2
GROUP BY mt2.seq1, mt2.uid
ORDER BY mt2.seq1 ASC
LIMIT 1
)
GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_id

This seems like it works, but it is abominably slow, running on the
order of days across 1.5 million rows rather than the seconds (or
preferably milliseconds) I'd prefer.

I have this annoying feeling that I'm overlooking a reasonably
efficient in-between query.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005