From: | "Mihai Gheorghiu" <tanethq(at)earthlink(dot)net> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Surprise :-( |
Date: | 2002-09-06 17:59:10 |
Message-ID: | 00ac01c255cf$1b12bfc0$6e646464@New6.Travel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
PG7.1.3 on RH7.1 on Dell PowerEdge 2500SC P3-933, 1GB RAM, 18GB SCSI160
There are 350k rows with trxtype=MP
With indexscan=off:
Aggregate (cost=22975.15..22990.27 rows=302 width=24)
-> Group (cost=22975.15..22982.71 rows=3025 width=24)
-> Sort (cost=22975.15..22975.15 rows=3025 width=24)
-> Seq Scan on tbas_transactions (cost=0.00..22800.29
rows=3025 width=24)
Time: 25.9s
With indexscan=on:
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)
Time: 24.9s
The point is I need to run this query in a fraction of the above time,
otherwise I'm in deep trouble.
Any suggestion is welcome.
-----Original Message-----
From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Mihai Gheorghiu <tanethq(at)earthlink(dot)net>
Cc: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Date: Thursday, September 05, 2002 6:51 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.
>
>If you set enable_indexscan=off; and then run explain/run the query
>is it better?
>
>For other questions, how many rows have trxtype='MP' really and what
>version are you running?
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Gribble | 2002-09-06 18:06:47 | Re: The Last Optimization |
Previous Message | scott.marlowe | 2002-09-06 17:25:41 | Re: The Last Optimization |