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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
