Re: agregates

Поиск
Список
Период
Сортировка
От codeWarrior
Тема Re: agregates
Дата
Msg-id b0mo75$11ne$1@news.hub.org
обсуждение исходный текст
Ответ на agregates  ("David Blood" <david@matraex.com>)
Список pgsql-general
Дерево обсуждения 
SELECT  paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT 1
OFFSET 1



""David Blood"" <david@matraex.com> wrote in message
news:016b01c2c238$b40ca580$1f00a8c0@redwood...
> 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--
>
>

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Lukacik
Дата:
Сообщение: Inquiry From Form [pgsql]
Следующее
От: Medi Montaseri
Дата:
Сообщение: Re: embedded postgres