Using max() MUCH slower in v7.1

Поиск
Список
Период
Сортировка
От Gordan Bobic
Тема Using max() MUCH slower in v7.1
Дата
Msg-id 001501c0809e$3b477220$8000000a@localdomain
обсуждение исходный текст
Ответы Re: Using max() MUCH slower in v7.1
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Henshall, Stuart - WCP"
Дата:
Сообщение: RE: MySQL file system
Следующее
От: Lamar Owen
Дата:
Сообщение: Re: RE: Why is there so much MySQL bashing???