Re: selects from large tables - Mailing list pgsql-performance

From Tom Lane
Subject Re: selects from large tables
Date
Msg-id 29735.1037636727@sss.pgh.pa.us
Whole thread Raw
In response to Re: selects from large tables  ("Charles H. Woloszynski" <chw@clearmetrix.com>)
List pgsql-performance
"Charles H. Woloszynski" <chw@clearmetrix.com> writes:
> Are you doing vaccums on these tables?  I was under the understanding
> that the estimated row count should be close to the real row count
> returned, and when it is not (as it looks in your case), the primary
> reason for the disconnect is that the stats for the tables are
> out-of-date.

The fact that he's using 7.1 doesn't help any; the statistics mechanisms
in 7.1 are pretty weak compared to 7.2.

> Also, do you do any clustering of the data (since the queries are mostly
> time limited)?  I am wondering if the system is doing lots of seeks to
> get the data (implying that the data is all over the disk and not
> clustered).

It would also be interesting to try a two-column index ordered the other
way (timestamp as the major sort key instead of ID).  Can't tell if that
will be a win without more info about the data properties, but it's
worth looking at.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Charles H. Woloszynski"
Date:
Subject: Re: selects from large tables
Next
From: Stephan Szabo
Date:
Subject: Re: selects from large tables