Re: using LIMIT only on primary table

In article <3D42D7AA(dot)27447(dot)3EE190A0(at)localhost>, "Dan Langille" wrote:
> This is an extension of the problem solved by
> http://archives.postgresql.org/pgsql-sql/2002-03/msg00020.php but
> with a slightly different complication.
>
> I want to get the last 100 port commits from the database. Commits
> are stored in
> commit_log and commit_log_ports relates commits to ports. A given
> commit may
> affect more than one port (i.e. there is a 1-N relationship between
> commit_log and
> commit_log_ports).
>

> So a starting point for the last 100 port commits is:
>
> explain analyze
> SELECT distinct commit_log.*
> FROM commit_log_ports, commit_log
> WHERE commit_log.id = commit_log_ports.commit_log_id
> ORDER BY commit_log.commit_date DESC, commit_log_ports.commit_log_id
> LIMIT 100;
>

I am not sure if this will help, but how about a subselect?

SELECT DISTINCT commit_log.*
FROM commit_log_ports,
(SELECT commit_log.id
FROM commit_log
ORDER BY commit_log.commit_date DESC
LIMIT 100) AS commit_log
WHERE commit_log.id = commit_log_ports.commit_log_id
ORDER BY commit_log.commit_date DESC, commit_log.id
LIMIT 100;