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

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@investsystems.co.uk>
To: Mihai Gheorghiu <tanethq@earthlink.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.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
>
>
>