PgSQl 7.4.7 podivnost u SUM()

Ing. Pavel PaJaSoft Janoušek PaJaSoft na FoNet.Cz
Pondělí Duben 25 10:15:16 CEST 2005


Honza Pazdziora <mailto:adelton na fi.muni.cz> wrote:
> A explain ukazuje co?

Rozdíl je pouze jen ve _způsobu_ scanu (index vs. seq)

 explain analyse SELECT sum(castka) as stav, current_date FROM pokladny
WHERE (datum<current_date) AND (storno=0::int2);
                                                                 QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------------------------
 Aggregate  (cost=1763.60..1763.60 rows=1 width=8) (actual
time=208.290..208.291 rows=1 loops=1)
   ->  Index Scan using pokladny_datumx on pokladny  (cost=0.00..1692.09
rows=28602 width=8) (actual time=0.176..155.556 rows=85805 loops=1)
         Index Cond: (datum < (('now'::text)::date)::timestamp without time
zone)
         Filter: (storno = 0::smallint)
 Total runtime: 208.470 ms
(5 řádek)


 explain analyse SELECT sum(castka) as stav, current_date FROM pokladny
WHERE (datum<'4/22/2005') AND (storno=0::int2);
                                                     QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------
 Aggregate  (cost=3192.49..3192.50 rows=1 width=8) (actual
time=149.482..149.482 rows=1 loops=1)
   ->  Seq Scan on pokladny  (cost=0.00..2978.06 rows=85773 width=8) (actual
time=0.011..102.183 rows=85737 loops=1)
         Filter: ((datum < '2005-04-22 00:00:00'::timestamp without time
zone) AND (storno = 0::smallint))
 Total runtime: 149.533 ms

ale hezky jsi mne nakopnul 

npersonal=#  SELECT sum(castka) as stav, current_date FROM pokladny WHERE
(datum<'4/25/2005'::date) AND (storno=0::int2);
       stav       |    date
------------------+------------
 262675.999999999 | 2005-04-25
(1 řádka)

npersonal=#  SELECT sum(castka) as stav, current_date FROM pokladny WHERE
(datum<'4/25/2005'::text::date) AND (storno=0::int2);
  stav  |    date
--------+------------
 262676 | 2005-04-25
(1 řádka)

-------------------------------------------------------------------
Ing. Pavel Janousek (PaJaSoft)             FoNet, spol. s r. o.
Technicka podpora, Intranet/Internet     Sokolova 67, 619 00 Brno
E-mail: mailto:Janousek na FoNet.Cz         Tel.: +420  5  4324 4749
WWW:    http://WWW.FoNet.Cz/           E-mail: mailto:Info na FoNet.Cz
-------------------------------------------------------------------  



Další informace o konferenci Test