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.
>
Yes, drop the index on trxtype so that it's selecting on isposted. Obviously,
knowing things like the actual number of rows returned by explain analyze (only
7.2.x?) as per my other message would be more usedul.
Your trxtype index is actually selecting 50% of the rows but thinks it only has
3025...hmmmm...typing that made me think something is wrong...ah, I see 7.1.3,
not 7.2.x...how many other values for this field are there? Even 7.1.3 should
have caught that as a common value right? However, in short, unless isposted is
true for lots of those 350k rows you dropping the index would hopefully prompt
a switch to an index on that column. Or, create a multicolumn index on
(isposted,trxtype).
BTW, there must be some sort of data clustering going because that index scan
is faster than the sequential scan even though it's fetching 50% of the
table. Perhaps it's a result of caching.
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
