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

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?

-----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: Thursday, September 05, 2002 7:07 PM
Subject: Re: [GENERAL] Surprise :-(


>On Thu, 5 Sep 2002, Mihai Gheorghiu wrote:
>
>>
>> explain select account, sum(amount) from tbas_transactions where isposted
>> and trxtype = 'MP' group by account;
>> psql:xx.txt:1: NOTICE:  QUERY PLAN:
>>
>> Aggregate  (cost=10874.64..10889.76 rows=302 width=24)
>>   ->  Group  (cost=10874.64..10882.20 rows=3025 width=24)
>>         ->  Sort  (cost=10874.64..10874.64 rows=3025 width=24)
>>               ->  Index Scan using trx_trxtype_idx on tbas_transactions
>> (cost=0.00..10699.78 rows=3025 width=24)
>>
>> EXPLAIN
>>
>> 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.
>>
>>
>> -----Original Message-----
>> From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
>> To: Mihai Gheorghiu <tanethq@earthlink.net>
>> Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
>> Date: Thursday, September 05, 2002 4:34 PM
>> Subject: Re: [GENERAL] Surprise :-(
>>
>>
>> >On Thu, 5 Sep 2002, Mihai Gheorghiu wrote:
>> >
>> >> I ran a vacuum analyze on a database. Now the query I ran vacuum
analyze
>> for
>> >> takes twice as long, and all the other queries I tested take longer,
too.
>> >> Please help.
>> >
>> >What are the queries and explain output for the queries (preferably
>> >including the old state if you have explain from that as well).
>> >
>
>Okay, so, on the face of it it's a pretty good plan, it has chosen an index
>scan returning only 3025 tuples out of 7000,000 after all. What does
EXPLAIN
>ANALYZE <your query> show?
>
>Also you could try and replicate the previous plan by doing a SET
>ENABLE_INDEXSCAN = OFF. Although this is a unlikely to give the previous
plan
>imo. A better approach would be to drop the index currently used, once sure
of
>being able to recreate it of course, and repeating as necessary to see
which
>index was giving the previous level performance.
>
>What does SELECT * FROM pg_statistic WHERE starelid = (SELECT oid FROM
pg_class
>WHERE relname = 'tbas_transactions'); give? Don't forget to indicate which
>column of your table has which attribute number in that output.
>
>Assuming the name of the index used is descriptive the difference must be
due
>to isposted being true being more selective than the trxtype test. It
starts to
>get difficult at this point without knowing the explain analyze results and
>something like SELECT isposted, count(1) FROM tbas_transactions GROUP BY
>isposted.
>
>
>--
>Nigel J. Andrews
>Director
>
>---
>Logictree Systems Limited
>Computer Consultants
>
>
>
>