PostgreSQL a LC_TIME

[Otakar Kleps]

Pavel Stehule wrote:
>>Trosku z jineho soudku - v tom pripade me zajima, proc to co oznacujes 
>>za "blby napad" ma v sobe postgres pravdepodobne implemnentovano prave 
>>pri praci s casem. Neni totiz jedno, kdyz napisu podminku 
>>_date=CURRENT_DATE a nebo _date='2005-04-06'::date(kde _date je typ date).
>>
> 
> 
> work=# work=# explain analyze select * from xx where d=current_date;
>                                          QUERY PLAN
> --------------------------------------------------------------------------------------------
>  Seq Scan on xx  (cost=0.00..0.00 rows=1 width=4) (actual 
> time=0.003..0.003 rows=0 loops=1)
>    Filter: (d = '2003-04-05'::date)
>  Total runtime: 0.091 ms
> (3 rows)
> 
> work=# explain analyze select * from xx where d=current_date;
>                                          QUERY PLAN
> --------------------------------------------------------------------------------------------
>  Seq Scan on xx  (cost=0.00..0.00 rows=1 width=4) (actual 
> time=0.003..0.003 rows=0 loops=1)
>    Filter: (d = ('now'::text)::date)
>  Total runtime: 0.057 ms
> (3 rows)
> 
> PostgreSQL rozhodne nevyhodnocuje CURRENT_DATE pro kazdy zaznam. Byl by to 
> i nesmysl. Now se vyhodnoti pouze jednou a to pri sestaveni planu.

Tak jinak:
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?!
--
Ota Kleps

> Pavel Stehule