Joseph Shraibman <[EMAIL PROTECTED]> writes:
> Basically there is the utable, which has two keys in it. I want to
> select some rows from the utable. Some columns come from the dtable,
> which has a key that matches to one of the utable keys, and I want to
> select the last value from the mltable which has the same two keys as
> utable and it has a serial value that I use do determine which is the
> last entry. I also want to use the last value from the mltable in the
> WHERE clause.
I'm thinking you could use a subselect along the lines of
select distinct on (ml.f1,ml.f2) * from ut,ml
where ut.f1 = ml.f1 and ut.f2 = ml.f2
order by ml.f1, ml.f2, ml.f3 desc;
f1 and f2 being the match keys and f3 being the serial value.
Given two-key indexes, I get plans along the lines of
Unique (cost=109.96..110.08 rows=2 width=20)
-> Sort (cost=109.96..109.96 rows=25 width=20)
-> Merge Join (cost=0.00..109.38 rows=25 width=20)
-> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000 width=8)
-> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000 width=12)
which doesn't look too bad.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
