| From: | "codeWarrior" <GPatnude(at)adelphia(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: agregates |
| Date: | 2003-01-22 18:41:11 |
| Message-ID: | b0mo75$11ne$1@news.hub.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
SELECT paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT 1
OFFSET 1
""David Blood"" <david(at)matraex(dot)com> wrote in message
news:016b01c2c238$b40ca580$1f00a8c0(at)redwood(dot)(dot)(dot)
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> Content-Type: text/plain;
> charset="US-ASCII"
> Content-Transfer-Encoding: 7bit
>
>
> 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
>
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> Content-Type: text/html;
> charset="US-ASCII"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
charset=3Dus-ascii">
> <TITLE>Message</TITLE>
>
> <META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I would
l=
> ike to be a=20
> ble to get a bunch of payments from a payment table. I want to
return=
> not=20
> the last payment the payment before that for all
customers.</FONT></SPAN></=
> DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV>
> <DIV><SPAN class=3D096202915-22012003>I have tried</SPAN></DIV><SPAN=20
> class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>select max(paymentid) as=20
> paymentid<BR> from tblpayment
<BR> where=
> =20
> paymentdebit > 0<BR> and paymentid not in (select=20
> max(paymentid) as paymentid<BR> from tblpayment=20
> <BR> where paymentdebit > 0<BR> group
=
> by=20
> customerid)</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV></SPAN><SPAN class=3D096202915-22012003><FONT face=3DArial
size=3D2>th=
> e cost on=20
> this thru the roof</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>NOTICE: QUERY
PLAN:</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Aggregate
(cost=3D1520829785.44..152=
> 0829785.44=20
> rows=3D1 width=3D4)<BR> -> Seq Scan on tblpayment =20
> (cost=3D0.00..1520829707.94 rows=3D31002=20
> width=3D4)<BR> =20
>
SubPlan<BR> ->&nbs=
> p;=20
> Materialize (cost=3D10336.74..10336.74 rows=3D6200=20
>
width=3D8)<BR> &=
> nbsp; =20
> -> Aggregate (cost=3D10026.72..10336.74 rows=3D6200=20
>
width=3D8)<BR> &=
> nbsp; =20
> -> Group (cost=3D10026.72..10181.73 rows=3D62005=20
>
width=3D8)<BR> &=
>
nbsp; &nbs=
> p; =20
> -> Sort (cost=3D10026.72..10026.72 rows=3D62005=20
>
width=3D8)<BR> &=
>
nbsp; &nbs=
> p; =20
> -> Seq Scan on tblpayment (cost=3D0.00..5091.10
rows=3D62005=
> =20
> width=3D8)<BR></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>the same
=
> query=20
> rewritten using exist has a better time but still much to=20
> long</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> size=3D2></FONT></SPAN> </DIV><SPAN class=3D096202915-22012003>
> <DIV><BR><FONT face=3DArial size=3D2>NOTICE: QUERY
PLAN:</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Aggregate
(cost=3D5769119.39..576927=
> 4.41=20
> rows=3D3100 width=3D8)<BR> -> Group =20
> (cost=3D5769119.39..5769196.90 rows=3D31002=20
> width=3D8)<BR> ->
Sort&n=
> bsp;=20
> (cost=3D5769119.39..5769119.39 rows=3D31002=20
>
width=3D8)<BR> &=
> nbsp; =20
> -> Seq Scan on tblpayment thismonth
(cost=3D0.00..5766806.60=
> =20
> rows=3D31002=20
>
width=3D8)<BR> &=
> nbsp; =20
>
SubPlan<BR> &nbs=
> p; =20
> -> Limit (cost=3D39.16..39.16 rows=3D1=20
>
width=3D12)<BR> =
>
&nb=
> sp; =20
> -> Sort (cost=3D39.16..39.16 rows=3D5=20
>
width=3D12)<BR> =
>
&nb=
> sp; =20
> -> Index Scan using tblpayment_idx on tblpayment =20
> (cost=3D0.00..39.11 rows=3D5 width=3D12)</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>EXPLAIN<BR></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>is there
=
> a better=20
> way to get the max - 1?</FONT></SPAN></DIV>
> <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I could
l=
> oop through=20
> and run the queriy for each customer but the cost on ten thousand quesries
=
> is=20
> rather high also?</FONT></SPAN></DIV></SPAN></SPAN>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> <DIV> </DIV></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> <DIV> </DIV></BODY></HTML>
>
> ------=_NextPart_000_016C_01C2C1FE.07ADCD80--
>
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | codeWarrior | 2003-01-22 18:55:38 | Re: agregates |
| Previous Message | Mike Mascari | 2003-01-22 18:40:26 | Odd subselect in target list behavior WRT aggregation |