Обсуждение: Indexes not always used after inserts/updates/vacuum analyze

I recently upgraded to 7.2 from 7.1.
Prior to 7,2, I was shutting down the database, droping indexes,
vacuuming analayze, re-building all the indexes on a nightly basis ( all
automated of course ;) ).  Things ran fine.
After upgrading to 7.2, I replaced all that with a nightly  on-line
/usr/local/pgsql/bin/vacuumdb -v -z  on the database.

The problems I am seeing is this.  As new data is added and modified,
indexes are not being used with some queries, but work fine on others.
 Even a vacuum full analyze did not fix the problem.  I had to drop and
re-build the index for the query to use the index. Now, I see the
problem starting again.  The table has about 20-30 million rows (
4-5Gigs in size), so seq scan kills any access.

This email is lengthy, but I wanted to detail this well.

Here is the table definition:

                Table "symbol_data"

       Column       |         Type          | Modifiers

--------------------+-----------------------+-----------

 symbol_name        | character varying(10) | not null
 date               | date                  | not null
 open               | numeric(15,3)         |
 high               | numeric(15,3)         |
 low                | numeric(15,3)         |
 d_close            | numeric(15,3)         |
 volume             | numeric(15,0)         |
 earnings           | numeric(15,3)         |
 dividend           | numeric(15,3)         |
 source             | character varying(50) |
 daily_mp_12a_long  | character(3)          |
 weekly_mp_12a_long | character(3)          |
 daily_mp_32a_long  | character(3)          |
 weekly_mp_32a_long | character(3)          |
Indexes: symbol_data_date_indx
Unique keys: symbol_data_pkey
------------------------------------------------------
Index "symbol_data_date_indx"
 Column | Type
--------+------
 date   | date
btree
------------------------------------------------------
    Index "symbol_data_pkey"
   Column    |         Type
-------------+-----------------------
 symbol_name | character varying(10)
 date        | date
unique btree
----------------------------------------------------------

Here is what I would expect which usually happens:

explain select * from symbol_data where symbol_name='IBM';
Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..512.99 rows=128 width=129)

Here is one that fails:

explain select * from symbol_data where symbol_name='ELTE';
Seq Scan on symbol_data  (cost=0.00..707415.32 rows=438015 width=129)

Now I thought maybe it had something to do with the concatenated primary key, but:
 explain select * from symbol_data where symbol_name='IBM' and date between '1990-01-01' and '2002-01-01';
 Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..402.55 rows=100 width=129)

explain select * from symbol_data where symbol_name='ELTE' and date between '1990-01-01' and '2002-01-01';
Seq Scan on symbol_data  (cost=0.00..810075.06 rows=342903 width=129)

Now, changing the date range will eventually use the index:

explain select * from symbol_data where symbol_name='ELTE' and date between '2002-01-01' and '2002-02-01';
NOTICE:  QUERY PLAN:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..10815.42 rows=2706 width=129)




...now I do a vacuum analyze:
 VACUUM verbose ANALYZE symbol_data;
NOTICE:  --Relation symbol_data--
NOTICE:  Index symbol_data_date_indx: Pages 49709; Tuples 20536054: Deleted 4221.
    CPU 4.35s/16.30u sec elapsed 45.33 sec.
NOTICE:  Index symbol_data_pkey: Pages 74029; Tuples 20536054: Deleted 4221.
    CPU 6.44s/15.15u sec elapsed 31.00 sec.
NOTICE:  Removed 4221 tuples in 70 pages.
    CPU 0.00s/0.04u sec elapsed 0.08 sec.
NOTICE:  Pages 450911: Changed 0, Empty 0; Tup 20536054: Vac 4221, Keep 0, UnUsed 1858963.
    Total CPU 49.20s/36.31u sec elapsed 149.00 sec.
NOTICE:  Analyzing symbol_data
VACUUM

explain select * from symbol_data where symbol_name='ELTE' and date between '1990-01-01' and '2002-01-01';
Seq Scan on symbol_data  (cost=0.00..810291.94 rows=292916 width=129)

No change on the name.

A new index:

 create index test on symbol_data (symbol_name);

explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Seq Scan on symbol_data  (cost=0.00..707611.68 rows=383340 width=129)

EXPLAIN
vpm=> VACUUM verbose ANALYZE  symbol_data;
NOTICE:  --Relation symbol_data--
NOTICE:  Pages 450911: Changed 0, Empty 0; Tup 20536054: Vac 0, Keep 0, UnUsed 1863184.
    Total CPU 38.99s/4.50u sec elapsed 67.95 sec.
NOTICE:  Analyzing symbol_data
VACUUM
vpm=>  explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Seq Scan on symbol_data  (cost=0.00..707611.68 rows=355958 width=129)

Doesnt works.  I think the only way to make this ever work is to drop the indexes, vacuum full, and rebuild.

Any thoughts?  This is a very dynamic table, but I was hoping the online vacuum in 7.2 would fix the problems.

I'm in the process of splitting this table up into smaller pieces which will make life easier anyway,
but I think there is something going on here.

Thanks,
Michael

"Michael G. Martin" <michael@vpmonline.com> writes:
> Here is what I would expect which usually happens:

> explain select * from symbol_data where symbol_name='IBM';
> Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..512.99 rows=128 width=129)

> Here is one that fails:

> explain select * from symbol_data where symbol_name='ELTE';
> Seq Scan on symbol_data  (cost=0.00..707415.32 rows=438015 width=129)

The planner thinks that there are 438K occurrences of 'ELTE' in your
table.  If that's true, a sequential scan is not obviously the wrong
choice.  How many are there, in reality?  What do you get from

select * from pg_stats where tablename = 'symbol_data' and attname =
'symbol_name';

Also, to put the rubber to the road: if you force an indexscan by
doing "set enable_seqscan = off", does it get faster or slower?
(EXPLAIN ANALYZE would be useful here.)

            regards, tom lane

Hi Tom,

Here is what is actually there:

 select count(*) from symbol_data where symbol_name='ELTE';
 count
-------
   687

Here is the pg_stat query:


 select * from pg_stats where tablename = 'symbol_data' and attname ='symbol_name';
  tablename  |   attname   | null_frac | avg_width | n_distinct |               most_common_vals               |                                               most_common_freqs                                               |                  histogram_bounds                   | correlation
-------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
 symbol_data | symbol_name |         0 |         7 |     152988 | {EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} | {0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333} | {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
(1 row)


Interesting eh? EBALX also does a full scan--all others in the above list get an index scan.

Here's the  variable stuff--I attached the verbose outputs.
 set enable_seqscan = on;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Seq Scan on symbol_data  (cost=0.00..707611.68 rows=355958 width=129)

 set enable_seqscan = off;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..1420899.51 rows=355958 width=129)

Index scan appears slower in explain, but the rows value is weird.

Thanks,
Michael


Tom Lane wrote:

"Michael G. Martin" <michael@vpmonline.com> writes:

Here is what I would expect which usually happens:

explain select * from symbol_data where symbol_name='IBM';
Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..512.99 rows=128 width=129)

Here is one that fails:

explain select * from symbol_data where symbol_name='ELTE';
Seq Scan on symbol_data  (cost=0.00..707415.32 rows=438015 width=129)

The planner thinks that there are 438K occurrences of 'ELTE' in your
table.  If that's true, a sequential scan is not obviously the wrong
choice.  How many are there, in reality?  What do you get from

select * from pg_stats where tablename = 'symbol_data' and attname =
'symbol_name';

Also, to put the rubber to the road: if you force an indexscan by
doing "set enable_seqscan = off", does it get faster or slower?
(EXPLAIN ANALYZE would be useful here.)

			regards, tom lane

"Michael G. Martin" <michael@vpmonline.com> writes:
> Here is what is actually there:

>  select count(*) from symbol_data where symbol_name='ELTE';
>    687

Hmm.  Do you have reason to think that that was also true when you last
did VACUUM ANALYZE or VACUUM?

> Here is the pg_stat query:
>  select * from pg_stats where tablename = 'symbol_data' and attname
> ='symbol_name';
>   tablename  |   attname   | null_frac | avg_width | n_distinct
> |               most_common_vals
> |
> most_common_freqs
> |                  histogram_bounds                   | correlation
>
-------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
>  symbol_data | symbol_name |         0 |         7 |     152988 |
> {EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
> {0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
> | {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
> (1 row)

What this says is that in the last ANALYZE, EBALX accounted for 18% of
the sample, and ELTE for 17%.  Does that seem plausible to you?  If the
sample was accurate then I'd agree with the planner's choices.  It'd
seem that either your table contents are changing drastically (in which
case more-frequent ANALYZEs may be the answer), or you had the bad luck
to get a very unrepresentative sample, or there's some bug in the
statistical calculations.

            regards, tom lane

yes.  each symbol_name only gets one row added and maybe a few updated
each market day.
This is interesting too.  Planner thinks 128 rows on this symbol, GE,
yet there are really 5595.  Not as off as ELTE, but a large factor.  at
least the index get hit here.

explain select * from symbol_data where symbol_name='GE';
NOTICE:  QUERY PLAN:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..513.09
rows=128 width=129)

EXPLAIN
vpm=> select count(*) from  symbol_data where symbol_name='GE';
 count
-------
  5595



Tom Lane wrote:

>"Michael G. Martin" <michael@vpmonline.com> writes:
>
>>Here is what is actually there:
>>
>
>> select count(*) from symbol_data where symbol_name='ELTE';
>>   687
>>
>
>Hmm.  Do you have reason to think that that was also true when you last
>did VACUUM ANALYZE or VACUUM?
>
>>Here is the pg_stat query:
>> select * from pg_stats where tablename = 'symbol_data' and attname
>>='symbol_name';
>>  tablename  |   attname   | null_frac | avg_width | n_distinct
>>|               most_common_vals
>>|
>>most_common_freqs
>>|                  histogram_bounds                   | correlation

>>-------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
>> symbol_data | symbol_name |         0 |         7 |     152988 |
>>{EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
>>{0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
>>| {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
>>(1 row)
>>
>
>What this says is that in the last ANALYZE, EBALX accounted for 18% of
>the sample, and ELTE for 17%.  Does that seem plausible to you?  If the
>sample was accurate then I'd agree with the planner's choices.  It'd
>seem that either your table contents are changing drastically (in which
>case more-frequent ANALYZEs may be the answer), or you had the bad luck
>to get a very unrepresentative sample, or there's some bug in the
>statistical calculations.
>
>            regards, tom lane
>

I said:
>> symbol_data | symbol_name |         0 |         7 |     152988 |
>> {EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
>> {0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
>> | {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
>> (1 row)

> What this says is that in the last ANALYZE, EBALX accounted for 18% of
> the sample, and ELTE for 17%.

Argh, make that 1.8% and 1.7%.

That's still orders of magnitude away from what you say the correct
frequency is, however: 687 out of 20+ million.  I'd like to think that
the statistical sampling would be unlikely to make such a large error.

            regards, tom lane

Sorry, I missed your bottom part before I replied last.

The table breakdown consists of about 8000 symbol_names with at most
5000 rows of data for each symbol ( stock market history ).

So, those sample percents seem huge.  The most any symbol would have
would be about 5000 / (8000*5000) = .0125%

--Michael



Tom Lane wrote:

>"Michael G. Martin" <michael@vpmonline.com> writes:
>
>>Here is what is actually there:
>>
>
>> select count(*) from symbol_data where symbol_name='ELTE';
>>   687
>>
>
>Hmm.  Do you have reason to think that that was also true when you last
>did VACUUM ANALYZE or VACUUM?
>
>>Here is the pg_stat query:
>> select * from pg_stats where tablename = 'symbol_data' and attname
>>='symbol_name';
>>  tablename  |   attname   | null_frac | avg_width | n_distinct
>>|               most_common_vals
>>|
>>most_common_freqs
>>|                  histogram_bounds                   | correlation

>>-------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
>> symbol_data | symbol_name |         0 |         7 |     152988 |
>>{EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
>>{0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
>>| {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
>>(1 row)
>>
>
>What this says is that in the last ANALYZE, EBALX accounted for 18% of
>the sample, and ELTE for 17%.  Does that seem plausible to you?  If the
>sample was accurate then I'd agree with the planner's choices.  It'd
>seem that either your table contents are changing drastically (in which
>case more-frequent ANALYZEs may be the answer), or you had the bad luck
>to get a very unrepresentative sample, or there's some bug in the
>statistical calculations.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>

Heh--i was gonna ask why the strange percent representation in the stats
table.

I just ran a vacuum analyze with the specific column.  Still get the
same explain plan:

Seq Scan on symbol_data  (cost=0.00..709962.90 rows=369782 width=129)

--Michael

Tom Lane wrote:

>I said:
>
>>>symbol_data | symbol_name |         0 |         7 |     152988 |
>>>{EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
>>>{0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
>>>| {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
>>>(1 row)
>>>
>
>>What this says is that in the last ANALYZE, EBALX accounted for 18% of
>>the sample, and ELTE for 17%.
>>
>
>Argh, make that 1.8% and 1.7%.
>
>That's still orders of magnitude away from what you say the correct
>frequency is, however: 687 out of 20+ million.  I'd like to think that
>the statistical sampling would be unlikely to make such a large error.
>
>            regards, tom lane
>

"Michael G. Martin" <michael@vpmonline.com> writes:
> I just ran a vacuum analyze with the specific column.  Still get the
> same explain plan:

Did the pg_stats data change noticeably?

ANALYZE is a statistical sampling process in 7.2, so I'd expect the
results to move around somewhat each time you repeat it.  But if it
changes a lot then we have a problem.

You could also try

ALTER TABLE symbol_data ALTER symbol_name SET STATISTICS n

for larger values of n (10 is the default) and then re-ANALYZE
to see if the stats get any more accurate.  The default of 10
was more or less picked out of the air ... perhaps it's too small.

            regards, tom lane

Here's the new stats since the vacuum on that column--quite a few changes.

 select * from pg_stats where tablename = 'symbol_data' and attname
='symbol_name';
  tablename  |   attname   | null_frac | avg_width | n_distinct
|               most_common_vals
|
most_common_freqs
|                   histogram_bounds                   | correlation

-------------+-------------+-----------+-----------+------------+----------------------------------------------+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------+-------------
 symbol_data | symbol_name |         0 |         7 |     150712 |
{EBALX,ELTE,SRP,KMG,MKC,AEN,BAC,BDX,BKF,BRT} |
{0.0233333,0.018,0.00266667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
| {A,BRN,DPAC,FUTR,INTC,MDSN,OCA,RAA,SSYS,USTB,_^^VPM} |    0.112971
(1 row)

I'll alter and play with the table tomorrow and let you know what I find.

-Michael

Tom Lane wrote:

>"Michael G. Martin" <michael@vpmonline.com> writes:
>
>>I just ran a vacuum analyze with the specific column.  Still get the
>>same explain plan:
>>
>
>Did the pg_stats data change noticeably?
>
>ANALYZE is a statistical sampling process in 7.2, so I'd expect the
>results to move around somewhat each time you repeat it.  But if it
>changes a lot then we have a problem.
>
>You could also try
>
>ALTER TABLE symbol_data ALTER symbol_name SET STATISTICS n
>
>for larger values of n (10 is the default) and then re-ANALYZE
>to see if the stats get any more accurate.  The default of 10
>was more or less picked out of the air ... perhaps it's too small.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>

On Wed, 27 Feb 2002 at 22:57, Tom Lane wrote:

> Also, to put the rubber to the road: if you force an indexscan by
> doing "set enable_seqscan = off", does it get faster or slower?
> (EXPLAIN ANALYZE would be useful here.)

I've just found a case where forcing indexscans results in much higher
speed. On the the 350000 rows table mentioned in my other post after a
"VACUUM FULL ANALYZE":

max=# set enable_seqscan to false;
max=# EXPLAIN analyze
    SELECT count(foo.id) FROM foo, bar
    WHERE foo.id = bar.ref2foo;
Aggregate    (cost=27513.65..27513.65 rows=1 width=8)
        (actual time=652.38..652.38 rows=1 loops=1)
  ->  Merge Join
        (cost=0.00..27417.57 rows=38431 width=8)
        (actual time=0.06..603.02 rows=38431 loops=1)
        ->  Index Scan using foo_pkey on foo
        (cost=0.00..25153.18 rows=352072 width=4)
        (actual time=0.03..157.57 rows=38432 loops=1)
        ->  Index Scan using idx_bar_ref2foo on bar
        (cost=0.00..807.74 rows=38431 width=4)
        (actual time=0.02..170.25 rows=38431 loops=1)
Total runtime: 652.58 msec
               ^^^^^^^^^^^
max=# set enable_seqscan to true;
max=# EXPLAIN analyze
    SELECT count(foo.id) FROM foo, bar
    WHERE foo.id = bar.ref2foo;

Aggregate
        (cost=18560.65..18560.65 rows=1 width=8)
        (actual time=4951.57..4951.57 rows=1 loops=1)
  ->  Hash Join
        (cost=911.39..18464.58 rows=38431 width=8)
        (actual time=653.26..4905.37 rows=38431 loops=1)
        ->  Seq Scan on foo
        (cost=0.00..9251.72 rows=352072 width=4)
        (actual time=0.02..769.60 rows=352072 loops=1)
        ->  Hash
        (cost=683.31..683.31 rows=38431 width=4)
        (actual time=140.60..140.60 rows=0 loops=1)
              ->  Seq Scan on bar
        (cost=0.00..683.31 rows=38431 width=4)
        (actual time=0.02..78.57 rows=38431 loops=1)
Total runtime: 4951.70 msec
               ^^^^^^^^^^^^

I've reproduced that several times. Even on a newly started postmaster
the query takes less than 2.5 seconds with seqscans swited off.

cu
    Reinhard

Ok, so this morning after the automated nightly vacuum -z -v on the
database, ELTE no longer appears in the pg_stats table, and the index is
picked no problem.  The table data has not changed since last eve.

However, now there is a new symbol which is behaving the same way--I.
 This symbol was just loaded into the database yesterday. There are
officially 4108 rows in the symbol_data table where symbol_name='I'.  I
bumped the STATISTICS value up to 100, re-analyzed, but the pg_stats
table still shows I first on the list with a value of 0.0182--didn't
change much from the original STATISTICS value of 10.

Here are the explain analyzes:

set enable_seqscan = on;
explain analyze select * from symbol_data where symbol_name='I' order by
date;
NOTICE:  QUERY PLAN:

Sort  (cost=811813.33..811813.33 rows=373904 width=129) (actual
time=93423.45..93427.02 rows=4108 loops=1)
  ->  Seq Scan on symbol_data  (cost=0.00..709994.20 rows=373904
width=129) (actual time=92483.55..93399.60 rows=4108 loops=1)
Total runtime: 93431.50 msec


 set enable_seqscan = off;
SET VARIABLE
vpm=> explain analyze select * from symbol_data where symbol_name='I'
order by date;
NOTICE:  QUERY PLAN:

Sort  (cost=1584564.49..1584564.49 rows=373904 width=129) (actual
time=129.38..133.01 rows=4108 loops=1)
  ->  Index Scan using symbol_data_pkey on symbol_data
(cost=0.00..1482745.36 rows=373904 width=129) (actual time=21.54..105.46
rows=4108 loops=1)
Total runtime: 137.55 msec


Even though the optimizer thinks the index will cost more, it does pick
it and use it with the performance expected when enable_seqscan = off;

-Michael


Tom Lane wrote:

>"Michael G. Martin" <michael@vpmonline.com> writes:
>
>>I just ran a vacuum analyze with the specific column.  Still get the
>>same explain plan:
>>
>
>Did the pg_stats data change noticeably?
>
>ANALYZE is a statistical sampling process in 7.2, so I'd expect the
>results to move around somewhat each time you repeat it.  But if it
>changes a lot then we have a problem.
>
>You could also try
>
>ALTER TABLE symbol_data ALTER symbol_name SET STATISTICS n
>
>for larger values of n (10 is the default) and then re-ANALYZE
>to see if the stats get any more accurate.  The default of 10
>was more or less picked out of the air ... perhaps it's too small.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>

Reinhard Max <max@suse.de> writes:
> I've just found a case where forcing indexscans results in much higher
> speed.

>         ->  Index Scan using foo_pkey on foo
>         (cost=0.00..25153.18 rows=352072 width=4)
>         (actual time=0.03..157.57 rows=38432 loops=1)

The major estimation error is evidently in this indexscan.  What
statistics does pg_stats show for this table?

            regards, tom lane

Good news.

I looked through the code and after a little debugging found that the
STATISTICS * 300 gives you the sample size of rows used to gather
statistics.
With the symbol_data table with 20million tuples and on this column with
about 8000 unique values, i needed a very large sample size.
Even with a STATISTICS of 500 ( 150,000  random rows) I still got a few
symbols with a most_common_freqs of .01 or so.
Bumping the STATISTICS to 1000 put the highest most_common_freqs at
0.00788667, so no seq scans now.
Not too much of a time difference in the analyze either--at least not an
impact.


The only strange thing I see is still the estimated rows returned.  The
index is picked, so I don't know that it matters.  Even though this
query has 688 tuples, the explain thinks 17k+:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..70648.22
rows=17700 width=129)

-Michael

>
> Tom Lane wrote:
>
>>"Michael G. Martin" <michael@vpmonline.com> writes:
>>
>>>I just ran a vacuum analyze with the specific column.  Still get the
>>>same explain plan:
>>>
>>
>>Did the pg_stats data change noticeably?
>>
>>ANALYZE is a statistical sampling process in 7.2, so I'd expect the
>>results to move around somewhat each time you repeat it.  But if it
>>changes a lot then we have a problem.
>>
>>You could also try
>>
>>ALTER TABLE symbol_data ALTER symbol_name SET STATISTICS n
>>
>>for larger values of n (10 is the default) and then re-ANALYZE
>>to see if the stats get any more accurate.  The default of 10
>>was more or less picked out of the air ... perhaps it's too small.
>>
>>            regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>

I've had this happen on 2 seperate servers now.

After reading the docs, I bumped up shared_buffers.  On one machine with
2G pyhsical ram, I set the param to use 1G of memory ( 131072 value), on
another machine with 800M of RAM, I set the value to about 500M ( 64000
).  ipcs shows the correct amounts allocated.

Both servers run fine for a bit, then at some point, the entire box
freezes.  Pings work, but nothing else does, so a hard reboot is necessary.

Any ideas.  Any limits on what you can set these to.  I thought these
values would leave plenty for the other stuff to run on the server.

Here is a top output before freezing:

 9:14pm  up 38 days, 12:47,  2 users,  load average: 4.78, 5.12, 4.91
101 processes: 99 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 24.1% user,  7.2% system,  0.0% nice, 68.1% idle
CPU1 states: 28.0% user,  6.4% system,  0.0% nice, 64.4% idle
Mem:   898892K av,  897300K used,    1592K free,       0K shrd,       0K buff
Swap:  819272K av,   65792K used,  753480K free                  805924K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
 3370 postgres   9   0  382M 382M  381M S     0.1 43.5  10:53 postmaster
32762 postgres  10   0  104M 104M  103M S    15.2 11.9  65:25 postmaster
 1226 postgres   9   0 54372  53M 52852 S     0.0  6.0   0:08 postmaster
 1334 postgres   9   0 47756  46M 46240 S     0.0  5.3   0:03 postmaster
 1181 postgres   9   0 46184  45M 44592 S     0.0  5.1   0:12 postmaster
 1227 postgres   9   0 39796  38M 38328 S     0.0  4.4   0:06 postmaster
 1228 postgres   9   0 25072  24M 23580 S     0.0  2.7   0:05 postmaster
 9082 postgres  10   0 16608  16M 15180 D     5.0  1.8   0:00 postmaster
 9084 postgres  10   0 14700  14M 13316 S     4.6  1.6   0:00 postmaster
 3244 postgres   9   0 13376  13M 12052 S     0.0  1.4   0:00 postmaster
32668 postgres   9   0 11488  11M 10224 S     0.0  1.2   0:02 postmaster
32669 postgres   9   0 11136  10M  9888 S     0.0  1.2   0:55 postmaster
 9085 postgres  15   0 10820  10M  9520 S     2.5  1.1   0:00 postmaster
 9087 postgres  18   0 10796  10M  9496 R     2.9  1.1   0:00 postmaster
 9086 postgres  16   0 10696  10M  9400 S     2.3  1.1   0:00 postmaster

Thanks,
Michael

I read an earlier post by Tom where he recommends 1/4 of physical ram.
 I will go to 1/5 to be safe and I assume it will be ok.  I'm guessing
my 50% was probably overkill.

--Michael

Michael G. Martin wrote:

> I've had this happen on 2 seperate servers now.
>
> After reading the docs, I bumped up shared_buffers.  On one machine
> with 2G pyhsical ram, I set the param to use 1G of memory ( 131072
> value), on another machine with 800M of RAM, I set the value to about
> 500M ( 64000 ).  ipcs shows the correct amounts allocated.
>
> Both servers run fine for a bit, then at some point, the entire box
> freezes.  Pings work, but nothing else does, so a hard reboot is
> necessary.
>
> Any ideas.  Any limits on what you can set these to.  I thought these
> values would leave plenty for the other stuff to run on the server.
>
> Here is a top output before freezing:
>
> 9:14pm  up 38 days, 12:47,  2 users,  load average: 4.78, 5.12, 4.91
> 101 processes: 99 sleeping, 2 running, 0 zombie, 0 stopped
> CPU0 states: 24.1% user,  7.2% system,  0.0% nice, 68.1% idle
> CPU1 states: 28.0% user,  6.4% system,  0.0% nice, 64.4% idle
> Mem:   898892K av,  897300K used,    1592K free,       0K shrd,
> 0K buff
> Swap:  819272K av,   65792K used,  753480K free
> 805924K cached
>
>  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
> 3370 postgres   9   0  382M 382M  381M S     0.1 43.5  10:53 postmaster
> 32762 postgres  10   0  104M 104M  103M S    15.2 11.9  65:25 postmaster
> 1226 postgres   9   0 54372  53M 52852 S     0.0  6.0   0:08 postmaster
> 1334 postgres   9   0 47756  46M 46240 S     0.0  5.3   0:03 postmaster
> 1181 postgres   9   0 46184  45M 44592 S     0.0  5.1   0:12 postmaster
> 1227 postgres   9   0 39796  38M 38328 S     0.0  4.4   0:06 postmaster
> 1228 postgres   9   0 25072  24M 23580 S     0.0  2.7   0:05 postmaster
> 9082 postgres  10   0 16608  16M 15180 D     5.0  1.8   0:00 postmaster
> 9084 postgres  10   0 14700  14M 13316 S     4.6  1.6   0:00 postmaster
> 3244 postgres   9   0 13376  13M 12052 S     0.0  1.4   0:00 postmaster
> 32668 postgres   9   0 11488  11M 10224 S     0.0  1.2   0:02 postmaster
> 32669 postgres   9   0 11136  10M  9888 S     0.0  1.2   0:55 postmaster
> 9085 postgres  15   0 10820  10M  9520 S     2.5  1.1   0:00 postmaster
> 9087 postgres  18   0 10796  10M  9496 R     2.9  1.1   0:00 postmaster
> 9086 postgres  16   0 10696  10M  9400 S     2.3  1.1   0:00 postmaster
>
> Thanks,
> Michael
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster