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 по дате отправления: