From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
Cc: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>, Wei Weng <wweng(at)kencast(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Is there a better way to do this? |
Date: | 2007-08-29 01:06:29 |
Message-ID: | 18882.1188349589@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Michael Glaesemann <grzm(at)seespotcode(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2007-08-29 01:28:41 | Re: Reliable and fast money transaction design |
Previous Message | Tom Lane | 2007-08-29 00:43:32 | Re: Question regarding autovacuum |