"Gordan Bobic" <gordan@freeuk.com> writes:
> CREATE VIEW LastDate AS
> SELECT Company,
> max(Date) AS Date
> FROM PastInvoices
> GROUP BY Company;
> And then call it like
> SELECT Date FROM LastDate ORDER BY Date WHERE Company = 'SomeCompany';
> It takes ABSOLUTELY forever. The table has about 25-30M records.
The problem is that 7.1 isn't pushing the WHERE restriction down into
the view. Check the 'rows' estimate for the indexscan: is it showing
the whole table being returned, or just a few rows? It's probably
applying the WHERE restriction at the top, after computing the complete
output of the view :-(
I had a note to see if this could be improved before 7.1, but haven't
gotten to it yet. At the moment it's a price we paid for the more
flexible implementation of views in 7.1...
regards, tom lane