Pushing limit into subqueries of a union

From: Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org>
To: pgsql-general(at)postgreSQL(dot)org
Subject: Pushing limit into subqueries of a union
Date: 2005-06-09 22:04:09
Message-ID: 42A8BCD9.1090307@chezphil.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Experts,

Here is another "how can I rewrite this to go faster" idea.

I have two tables T1 and T2 and a view V that is the UNION ALL of T1 and
T2. The tables have an editdate field, and I want to get the n most
recently changed rows:

select * from V order by editdate desc limit 40;

This seems to unconditionally read the whole of T1 and T2, so it is slow.

T1 and T2 both have indexes on the editdate attribute, so if I write

(select * from T1 order by editdate desc limit 40)
union all (select * from T2 order by editdate desc limit 40)
order by editdate desc limit 40;

I get the same results, about 1000 times faster.

I presume that PostgreSQL doesn't try to push the limit clause into the
subqueries of a UNION ALL in this way. I believe it is safe, isn't it?

Cheers, Phil.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Siebert 2005-06-09 22:07:18 Now() function
Previous Message Russ Brown 2005-06-09 21:49:53 Re: Version Control?