PostgreSQL a LC_TIME
Otakar Kleps
kleps na avonet.cz
Středa Duben 6 10:52:05 CEST 2005
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
Další informace o konferenci Test