Re: Using max() MUCH slower in v7.1 - Mailing list pgsql-general

From Tom Lane
Subject Re: Using max() MUCH slower in v7.1
Date
Msg-id 17696.979752550@sss.pgh.pa.us
Whole thread Raw
In response to Using max() MUCH slower in v7.1  ("Gordan Bobic" <gordan@freeuk.com>)
Responses Re: Using max() MUCH slower in v7.1  ("Robert B. Easter" <reaster@comptechnews.com>)
Re: Using max() MUCH slower in v7.1  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
"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

pgsql-general by date:

Previous
From: Jeffery L Post
Date:
Subject: Re: Time Formats
Next
From: Alfred Perlstein
Date:
Subject: Re: MySQL file system