Hi.
I have just upgraded from v7.0.3 to v7.1b3, and one of the things I am
noticing is that doing a max() query search seems to take forever.
For example, if I have a view like:
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. This
worked with acceptable speed on v7.0.3. I used pg_dump to port the data,
and I have just done a VACUUM ANALYZE. EXPLAIN says that it will use the
index for the Company field, which is right, but it still takes a
ridiculously long time (hours). All indices are BTREE (HASH index creation
fails with the "out of overflow pages" error), and the table is index on
both Company and Date.
OTOH, if I just use no view and do
SELECT Date FROM PastInvoices WHERE Company = 'SomeCompany' ORDER BY Date
DESC, LIMIT 1;
which does PRECISELY the same thing, that finishes in a fraction of a
second. This was the same speed that the max() view query ran at on v7.0.x.
Why such a sudden change?
Regards.
Gordan
