Using max() MUCH slower in v7.1

From: "Gordan Bobic" <gordan(at)freeuk(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Using max() MUCH slower in v7.1
Date: 2001-01-17 15:57:02
Message-ID: 001501c0809e$3b477220$8000000a@localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ricardo Campos Passanezi 2001-01-17 16:11:58 Comparision between POSTGRES and PostgreSQL
Previous Message Tom Lane 2001-01-17 15:55:22 Re: postgres 7.0.3 core dumps