Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using max() MUCH slower in v7.1

Using max() MUCH slower in v7.1

From
"Gordan Bobic"
Date:
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

Re: Using max() MUCH slower in v7.1

From
Tom Lane
Date:
"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

Re: Using max() MUCH slower in v7.1

From
"Robert B. Easter"
Date:
On Wednesday 17 January 2001 12:29, Tom Lane wrote:
> "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.

As long as you are selecting a specific company with that WHERE clause,
isn't that ORDER BY unnecessary?

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------

Re: Using max() MUCH slower in v7.1

From
Tom Lane
Date:
>> 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.

OK, I did something about this ...

            regards, tom lane