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.
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? |