Re: Analysis Function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



David Jarvis <thangalin@xxxxxxxxx> wrote:

> [...]
> Yes. Here are the variations I have benchmarked (times are best of three):

> Variation #0
> -no date field-
> Explain: http://explain.depesz.com/s/Y9R
> Time: 2.2s

> Variation #1
> date('1960-1-1')
> Explain: http://explain.depesz.com/s/DW2
> Time: 2.6s

> Variation #2
> date('1960'||'-1-1')
> Explain: http://explain.depesz.com/s/YuX
> Time: 3.1s

> Variation #3
> date(extract(YEAR FROM m.taken)||'-1-1')
> Explain: http://explain.depesz.com/s/1I
> Time: 4.3s

> Variation #4
> to_date( date_part('YEAR', m.taken)::text, 'YYYY' ) + interval '0 months' +
> interval '0 days'
> Explain: http://explain.depesz.com/s/fIT
> Time: 4.4s

> What I would like is along Variation #5:

> *PGTYPESdate_mdyjul(taken_year, p_month1, p_day1)*
> Time: 2.3s

> I find it interesting that variation #2 is half a second slower than
> variation #1.
> [...]

Have you tested DATE_TRUNC()?

Tim


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux