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