PostgreSQL a LC_TIME
Karel Zak
zakkr na zf.jcu.cz
Čtvrtek Duben 7 13:04:47 CEST 2005
On Wed, 2005-04-06 at 10:52 +0200, Otakar Kleps wrote:
> testdb=> CREATE TABLE test (
> _time timestamp with time zone,
> _platform character(5),
> _tld character(5)
> ) WITHOUT OIDS;
>
> .. pote pokusna tabulka naplnena cca milionem zaznamu ...
>
> testdb=> explain analyze SELECT count(*) from test where
> _time::date=CURRENT_DATE;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
> Aggregate (cost=27.51..27.51 rows=1 width=0) (actual
> time=5654.19..5654.20 rows=1 loops=1)
> -> Seq Scan on test (cost=0.00..27.50 rows=5 width=0) (actual
> time=0.06..5646.95 rows=9912 loops=1)
> Filter: ((_time)::date = ('now'::text)::date)
> Total runtime: 5654.34 msec
> (4 rows)
>
> testdb=> explain analyze SELECT count(*) from test where
> _time::date='2005-04-06'::date;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
> Aggregate (cost=25.01..25.01 rows=1 width=0) (actual
> time=3645.28..3645.28 rows=1 loops=1)
> -> Seq Scan on test (cost=0.00..25.00 rows=5 width=0) (actual
> time=0.04..3638.08 rows=9912 loops=1)
> Filter: ((_time)::date = '2005-04-06'::date)
> Total runtime: 3645.34 msec
> (4 rows)
>
> Rozdil mezi obema dotazy v cost jednotkach je velmi maly, ale ve
> vyslednem case velmi rozdilny. Zakonite me pak napada, ze
> "_time::date=CURRENT_DATE"<>"_time::date='2005-04-06'::date". Tak v cem
> je problem?!
Ne v tom to nebude, ale souhlasim, ze ten cas nutny na
('now'::text)::date je podezrely.
Zeptam se moudrejsich...
Karel
--
Karel Zak <zakkr na zf.jcu.cz>
Další informace o konferenci Test