Re: String manipulation

David Olbersen wrote:
>> SELECT substring(path from position(' ' in path) + 1) FROM (SELECT
>> split_part( path, ':', 2 ) AS path FROM paths) AS ss;
>
> That's cool enough except for the sub-select which would really slow
> down the query on a million+ row table. My solution is just
> reformatting data and doesn't add much if any over head to "SELECT
> path FROM paths".
>
> ...but that's all speculation, am I right? I'd like to revise my
> thinking if I'm wrong.
>

I could be wrong but I was thinking it would be more efficient to only
calculate the function once. I get these explain analyze results:

regression=# explain analyze SELECT substring(path from position(' ' in
path) + 1) FROM (SELECT split_part( path, ':', 2 ) AS path FROM paths)
AS ss;
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on paths (cost=0.00..1.09 rows=4 width=33) (actual
time=0.06..0.09 rows=4 loops=1)
Total runtime: 0.19 msec
(2 rows)

regression=# explain analyze SELECT substring(split_part( path, ':', 2 )
from position( ' ' in split_part( path, ':', 2 ) ) + 1) FROM paths;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on paths (cost=0.00..1.09 rows=4 width=33) (actual
time=0.09..0.13 rows=4 loops=1)
Total runtime: 0.22 msec
(2 rows)

In either case each row is hit once.

Joe