Re: Is there a better way to do this? - Mailing list pgsql-general

Michael Glaesemann <grzm@seespotcode.net> writes:
> The concatenation is probably less efficient: you're casting an int
> to text and then the text to interval with the concatenation you're
> using. I don't know how that compares in terms of cycles to the int *
> interval math, but efficiency isn't really the reason I would avoid it.

It's a *lot* less efficient: number times interval-constant is basically
three floating-point multiplies, whereas as you said, the other way
involves formatting the number as text, performing a string
concatenation, and then parsing the result to see whether it's legal
input for an interval and if so what its value is.  Here's a simple
experiment to compare the costs:

regression=# explain analyze select x from generate_series(1,100000) x;
                                                          QUERY PLAN
      

------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series x  (cost=0.00..12.50 rows=1000 width=4) (actual time=670.205..1337.351 rows=100000
loops=1)
 Total runtime: 1713.093 ms
(2 rows)

regression=# explain analyze select x * '1 day'::interval from generate_series(1,100000) x;
                                                          QUERY PLAN
      

------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 width=4) (actual time=664.579..1841.494 rows=100000
loops=1)
 Total runtime: 2222.444 ms
(2 rows)

regression=# explain analyze select (x || ' days')::interval from generate_series(1,100000) x;
                                                          QUERY PLAN
      

------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series x  (cost=0.00..25.00 rows=1000 width=4) (actual time=741.236..3015.771 rows=100000
loops=1)
 Total runtime: 3402.385 ms
(2 rows)

Subtracting off the overhead estimated by the first query, we arrive at
196k conversions/second using multiply vs 60k conversions/sec using
concatenation.  This is on a rather old and slow server, but the ratio
probably holds up on other hardware.  Allowing for a lot of noise in the
measurements (I didn't bother trying to average several measurements),
I'd say 2X to 4X slower is a good estimate.

As Michael says, the speed argument is really kinda minor compared
to the other ones, but it's real enough.

            regards, tom lane