I created an index over account and trxtype.
Here is the result of explain (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)
I checked, and the two-column index is there, in the pd_indexes table.
-----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: Friday, September 06, 2002 2:11 PM
Subject: Re: [GENERAL] Surprise :-(
>
>On Fri, 6 Sep 2002, Mihai Gheorghiu wrote:
>
>> 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.
>
>Hmm, in general a multicolumn index on (trxtype,account) might enable it
>to get rid of the sort step.
>
>
