Re: Surprise :-( - Mailing list pgsql-general

On Mon, 9 Sep 2002, Mihai Gheorghiu wrote:

> This is the result of the statistic/count query:
>  trxtype | count
> ---------+--------
>  MP      | 347529
> ...
>  RG      |  30438
> ...

> >
> >On Fri, 6 Sep 2002, Mihai Gheorghiu wrote:
> >
> >> I ran select from pg_statistics... as you advised
> >> The result is attached.
> >> Col#   Name
> >> 5      account
> >> 10     trxtype
> >> 15     amount
> >> 28     isposted

starelid,staattnum,staop,stanullfrac,stacommonfrac,stacommonval,staloval,stahival

---------,----------,------,------------,--------------,--------------------------,--------------------------,-----------------------------------
55256329,10,1058,0,0.0479733,RG,AS,XP

RG fraction of table = 30 / 700 = 5%   (as pg_statistics shows)
MP fraction of table = 350 / 700 = 50% (expected in pg_statistics)

May be someone else can verify that 7.1.3 had problems with stats gathering but
all this just suggests to me that the table hasn't been analysed. Are you sure
it was done? Try what I suggest below, to do just the one table, when you have
time and if it's still appropiate.

Also you could run the command:

UPDATE pg_statistics SET sttcommonval = 'MP', stacommonfrac = 0.5
  WHERE statrelid = 55256329 AND staattnum = 10;

which will make pg_statistics reflect reality more accurate. However, that is
just going to make the planner choose the seqscan plan over the index scan and
doesn't explain why the statistics weren't updated properly by your vacuum
analyze.

Are you also sure that this same query on the same data ran faster before the
vacuum? Perhaps the fast query used a different value in the trxtype test?

It boils down to something must have changed and if it's not the data it must
be the query.


[as a reminder...]
> >> >>
> >> >> explain select account, sum(amount) from tbas_transactions where
> isposted
> >> >> and trxtype = 'MP' group by account;
> >> >>
> >> >> Sorry, I do not have an explain from before vacuum analyze.
> >> >> The table has ~700k rows and indices on account, trxtype and a few
> other
> >> >> fields used in other queries.
> >


And an interesting item to perform...

> >However, as it stands I suggest you should do a
> >
> > VACUUM VERBOSE ANALYZE tbas_transactions
> >
> >and retest your slow query. If no significant improvement it would be a
> good
> >idea to show us the output of that vacuum command and the same pg_statistic
> >entries as before but taken after this vacuum.
> >


I hope the 7.2.2 install is going well. It will be interesting see what you
make of it regarding this issue. I can't see it taking a significantly shorter
time for this query on this data.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants