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

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