| 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> |
| Subject: | Re: Surprise :-( |
| Date: | 2002-09-06 18:11:36 |
| Message-ID: | 20020906110737.I81169-100000@megazone23.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Joachim Smit | 2002-09-06 18:20:35 | ODBC - Proxy |
| Previous Message | Bill Gribble | 2002-09-06 18:06:47 | Re: The Last Optimization |