Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group