From: | "David Blood" <david(at)matraex(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | agregates |
Date: | 2003-01-22 17:07:06 |
Message-ID: | 016b01c2c238$b40ca580$1f00a8c0@redwood |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I would like to be a ble to get a bunch of payments from a payment
table. I want to return not the last payment the payment before that
for all customers.
I have tried
select max(paymentid) as paymentid
from tblpayment
where paymentdebit > 0
and paymentid not in (select max(paymentid) as paymentid
from tblpayment
where paymentdebit > 0
group by customerid)
the cost on this thru the roof
NOTICE: QUERY PLAN:
Aggregate (cost=1520829785.44..1520829785.44 rows=1 width=4)
-> Seq Scan on tblpayment (cost=0.00..1520829707.94 rows=31002
width=4)
SubPlan
-> Materialize (cost=10336.74..10336.74 rows=6200 width=8)
-> Aggregate (cost=10026.72..10336.74 rows=6200
width=8)
-> Group (cost=10026.72..10181.73 rows=62005
width=8)
-> Sort (cost=10026.72..10026.72
rows=62005 width=8)
-> Seq Scan on tblpayment
(cost=0.00..5091.10 rows=62005 width=8)
the same query rewritten using exist has a better time but still much to
long
NOTICE: QUERY PLAN:
Aggregate (cost=5769119.39..5769274.41 rows=3100 width=8)
-> Group (cost=5769119.39..5769196.90 rows=31002 width=8)
-> Sort (cost=5769119.39..5769119.39 rows=31002 width=8)
-> Seq Scan on tblpayment thismonth
(cost=0.00..5766806.60 rows=31002 width=8)
SubPlan
-> Limit (cost=39.16..39.16 rows=1 width=12)
-> Sort (cost=39.16..39.16 rows=5
width=12)
-> Index Scan using tblpayment_idx on
tblpayment (cost=0.00..39.11 rows=5 width=12)
EXPLAIN
is there a better way to get the max - 1?
I could loop through and run the queriy for each customer but the cost
on ten thousand quesries is rather high also?
David Blood
Boise, ID
David Blood
Boise, ID
From | Date | Subject | |
---|---|---|---|
Next Message | Vegard Munthe | 2003-01-22 17:17:11 | RULE and more than 10 rewrites. |
Previous Message | Ian Barwick | 2003-01-22 17:03:57 | Re: DBD::Pg & DBD::PgPP Cpan question |