Re: Query optimizer 8.0.1 (and 8.0)

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql(at)mohawksoft(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Query optimizer 8.0.1 (and 8.0)
Date: 2005-02-04 22:23:27
Message-ID: 20050204222327.GK10437@ns.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

* pgsql(at)mohawksoft(dot)com (pgsql(at)mohawksoft(dot)com) wrote:
> I have the USA census TIGER database loaded, the WHOLE THING, the whole
> country. It isn't the biggest database, but it is about 40G before
> indexes. Every table is over a few million rows. I can quite honestly say,
> a sequential scan is almost never the right thing to do.

Cool, I've got it loaded here too w/ PostGIS. It's good stuff. :) I'm
curious what all you're doing with it and especially if you're using
mapserver on it or have found a way to identify roads at a more
street/city/state/country level, I've had problems doing that.

> I suspect that analyze only samples a very small amount of the database
> and gets the wrong idea about it. Is there a way to force analyze to
> sample more rows?

Yup:

alter table <blah> alter column <blah> set statistics <blah>

As I recall, the default is 100 for statistics, I'd say increase that
number to like 200 or 300 or more and see if it helps.

Stephen

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Rolf Sponsel 2005-02-04 22:26:28 Re: [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris.
Previous Message Tom Lane 2005-02-04 22:17:35 Re: Query optimizer 8.0.1 (and 8.0)