Hi,
On Fri, 1 Mar 2002 at 09:37, Tom Lane wrote:
> Reinhard Max <[EMAIL PROTECTED]> writes:
>
> > I'll tell my colleague (it's his test database, after all) that he
> > should take more realistic test data before complaining about bad
> > performance...
>
> Actually, is it unrealistic test data?
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.
E
Reinhard Max <[EMAIL PROTECTED]> writes:
>> The actual rows read from this indexscan seem to be many fewer than
>> the number of rows in the table. What are the ranges of the id
>> values in tables foo and bar? I'm wondering if the merge could have
>> stopped far short of the end of the foo tabl
Hi,
On Thu, 28 Feb 2002 at 16:10, Tom Lane wrote:
> > -> Index Scan using foo2_pkey on foo2
> > (cost=0.00..10387.79 rows=352072 width=4)
> > (actual time=0.26..174.32 rows=38432 loops=1)
>
> The actual rows read from this indexscan seem to be many fe
> Are these the addtional values you wanted to see?
Yes, but I just noticed something else strange:
> -> Index Scan using foo2_pkey on foo2
> (cost=0.00..10387.79 rows=352072 width=4)
> (actual time=0.26..174.32 rows=38432 loops=1)
The actual rows re
On Thu, 28 Feb 2002 at 10:15, Tom Lane wrote:
> Okay. It looks like foo.id has a pretty strong but not perfect
> descending order (the correlation statistic is -0.563276). The
> planner is evidently not rating that effect strongly enough.
Yes, that seems to be the reason. When I try
S
Reinhard Max <[EMAIL PROTECTED]> writes:
>> The major estimation error is evidently in this indexscan. What
>> statistics does pg_stats show for this table?
> See attached file.
Okay. It looks like foo.id has a pretty strong but not perfect
descending order (the correlation statistic is -0.563
Reinhard Max <[EMAIL PROTECTED]> 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 ma
On Thu, 28 Feb 2002 at 09:51, Tom Lane wrote:
> Reinhard Max <[EMAIL PROTECTED]> 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)
> >
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
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
spe
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 |
"Michael G. Martin" <[EMAIL PROTECTED]> 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
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:
[EMAIL PROTECTED]">
I sai
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) = .01
I said:
>> symbol_data | symbol_name | 0 | 7 | 152988 |
>> {EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
>>
>{0.018,0.017,0.0017,0.0017,0.0017,0.0013,0.0013,0.0013,0.0013,0.0013}
>
>> | {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^
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
"Michael G. Martin" <[EMAIL PROTECTED]> 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 *
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_d
"Michael G. Martin" <[EMAIL PROTECTED]> 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 sele
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/l
21 matches
Mail list logo
