Re: Common Sub-expression removal

Hi,

One thing that occurred to me when reading the "Ad Hoc Indexes"
thread was that PG doesn't seem to do much with tidying up common
sub-expressions (I'm not sure why I remembered about it as it's not
particularly related, strange). Anyway, as an example imagine I have a
large table that I want to do a self join on:

SELECT m1.source_ls_id, m1.movement_date, m2.movement_date
FROM bcms.source_movements m1, bcms.source_movements m2
WHERE m1.source_ls_id = m2.source_ls_id
AND m1.movement_date < m2.movement_date;

I get a plan that sorts the movements table twice, giving the correct
answer but taking a while to actually get it.

Merge Join (cost=58981120.56..138431232.17 rows=1498156785 width=12)
Merge Cond: (m1.source_ls_id = m2.source_ls_id)
Join Filter: (m1.movement_date < m2.movement_date)
-> Sort (cost=29490560.28..29889000.48 rows=159376080 width=8)
Sort Key: m1.source_ls_id
-> Seq Scan on source_movements m1
(cost=0.00..2874586.80 rows=159376080 width=8)
-> Sort (cost=29490560.28..29889000.48 rows=159376080 width=8)
Sort Key: m2.source_ls_id
-> Seq Scan on source_movements m2
(cost=0.00..2874586.80 rows=159376080 width=8)

The time I actually tend to notice it more is when the join is between
two expensive views; this test case is nice and easy to reason about
though.

Sam