Re: Surprise :-(

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
> I must admit I cannot make very much sense out of it. What does it tell?
> Thank you very much.
> P.S. I am running PG7.1.3. Is explain analyze an improvement in 7.2?
>
> >>
> >> explain select account, sum(amount) from tbas_transactions where isposted
> >> and trxtype = 'MP' group by account;
> >> psql:xx.txt:1: NOTICE: QUERY PLAN:
> >>
> >> 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.

First, I have been assuming you're working on a non-essential and/or
non-production database where doing such things as deleting indexes is an
acceptable cost to determine and attempt to fix the speed problems you are
experiencing. I wouldn't have suggested such things otherwise.

Second, I believe you also stated that number of rows in this table with
trxtype = 'MP' is about 350k, i.e. 50%. The pg_statistic output you shown
shows 'RG' as the most common value with low and high values as thinks 'AS' and
'XP'. I think based on this information the statistics stand a chance of being
incorrect and you should try and confirm the distribution of values in this
column. Doing

SELECT trxtype, count(1)
FROM tbas_transactions
GROUP BY trxtype
ORDER BY trxtype DESC
LIMIT 10

would be instructive. The limit number is somewhat arbitrary, the most
interesting results of that query will be the first and probably second row
returned and the row where trxtype is 'MP'.

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.

As for the data loading into 7.2.2 taking a long time. I can't really suggest
anything. You may find the 7.2.2 load does take less time than the 7.1.3 you
tested it on. I think everyone would also recommend doing the upgrade even with
this load time.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants