Обсуждение: Database Statistics???

Hello all,

I am a bit confused...I have a database which was performing very POORLY
selecting from a view (posted earlier) on one server but extremely fast on
another server...

I just backed up the database from the FAST server and loaded to the SLOW
server and it ran just as fast as it originally did...my questions are:

Are STATISTICS some how saved with the database??  if so, how do I UPDATE
view or update them?

Should I backup the data \ drop the database and reload it to make it get
new stats?? (vacuum analyze does nothing for this poor performing database)

Thanks-a-bunch.
--
View this message in context: http://www.nabble.com/Database-Statistics----tf4075655.html#a11583450
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

smiley2211 wrote:
> Hello all,
>
> I am a bit confused...I have a database which was performing very POORLY
> selecting from a view (posted earlier) on one server but extremely fast on
> another server...
>
> I just backed up the database from the FAST server and loaded to the SLOW
> server and it ran just as fast as it originally did...my questions are:
>
> Are STATISTICS some how saved with the database??  if so, how do I UPDATE
> view or update them?
>
> Should I backup the data \ drop the database and reload it to make it get
> new stats?? (vacuum analyze does nothing for this poor performing database)
>
> Thanks-a-bunch.
>
You can update statistics with the analyze or vacuum analyze command,
but I'd bet what you are seeing here is the effect of recreating the
indices that replaying a backup does.

On 2007-07-13 smiley2211 wrote:
> I am a bit confused...I have a database which was performing very
> POORLY selecting from a view (posted earlier) on one server but
> extremely fast on another server...

EXPLAIN ANALYZE'ing the query will show you the planner's estimates. The
query plans should give you an idea of what the problem actually is. Did
you already run ANALYZE on the database?

> I just backed up the database from the FAST server and loaded to the
> SLOW server and it ran just as fast as it originally did...my
> questions are:
>
> Are STATISTICS some how saved with the database??

Not with the database, but in the pg_statistic catalog, AFAIK.

> if so, how do I UPDATE view or update them?

You collect statistics by ANALYZE'ing either particular tables or the
entire database. They can be viewed in the pg_catalog.pg_statistic
table. However, viewing the query plans for your queries will probably
be more telling.

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

Thanks Tom and Scott...that worked for a NEW database but not on the original
SLOW database...meaning - I backed up the SLOW database and restored it to a
NEW database and the query ran EXTREMELY FAST :clap:

Scott - (your question - What was the size of the slow databases data store
compared to the
fast database? --- I am new, how do I know the size of the database (OS file
size ??))...is there an sp_helpdb equivalent command??

My EXPLAINS are under a previous thread:

Query is taking 5 HOURS to Complete on 8.1 version

Thanks...Michelle


Tom Arthurs wrote:
>
> smiley2211 wrote:
>> Hello all,
>>
>> I am a bit confused...I have a database which was performing very POORLY
>> selecting from a view (posted earlier) on one server but extremely fast
>> on
>> another server...
>>
>> I just backed up the database from the FAST server and loaded to the SLOW
>> server and it ran just as fast as it originally did...my questions are:
>>
>> Are STATISTICS some how saved with the database??  if so, how do I UPDATE
>> view or update them?
>>
>> Should I backup the data \ drop the database and reload it to make it get
>> new stats?? (vacuum analyze does nothing for this poor performing
>> database)
>>
>> Thanks-a-bunch.
>>
> You can update statistics with the analyze or vacuum analyze command,
> but I'd bet what you are seeing here is the effect of recreating the
> indices that replaying a backup does.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>

--
View this message in context: http://www.nabble.com/Database-Statistics----tf4075655.html#a11585080
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Am Freitag 13 Juli 2007 schrieb smiley2211:
> Hello all,
>
> I am a bit confused...I have a database which was performing very POORLY
> selecting from a view (posted earlier) on one server but extremely fast on
> another server...
>
> I just backed up the database from the FAST server and loaded to the SLOW
> server and it ran just as fast as it originally did...my questions are:
>
> Are STATISTICS some how saved with the database??  if so, how do I UPDATE
> view or update them?
>
> Should I backup the data \ drop the database and reload it to make it get
> new stats?? (vacuum analyze does nothing for this poor performing database)
>
> Thanks-a-bunch.

Try this on both machines:
select relname, relpages, reltuples
  from pg_class
 where relkind='i'
   order by relpages desc limit 20;

Compare the results, are relpages much higher on the slow machine?

If so, REINDEX DATABASE slow_database;

smiley2211 wrote:
>
> Thanks Tom and Scott...that worked for a NEW database but not on the original
> SLOW database...meaning - I backed up the SLOW database and restored it to a
> NEW database and the query ran EXTREMELY FAST :clap:

Have you ever vacuumed the DB?

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)