Re: Surprise :-(

This is the result of the statistic/count query:
trxtype | count
---------+--------
MP | 347529
AS | 92273
PR | 56664
TS | 37756
RG | 30438
PK | 24764
UP | 14930
EX | 10285
PD | 7817
OT | 4149
WW | 2948
PO | 2568
VO | 728
XP | 2
LF | 1
(15 rows)

-----Original Message-----
From: Nigel J. Andrews <nandrews(at)investsystems(dot)co(dot)uk>
To: Mihai Gheorghiu <tanethq(at)earthlink(dot)net>
Cc: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Date: Friday, September 06, 2002 6:44 PM
Subject: Re: [GENERAL] 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
>
>
>