Postgresql parse url parameters

Question

The following is how I currently parse parameters from an url query string. Does anyone know of a nicer and faster solution? Thx.

select
  id,
  url,
  split_part(regexp_split_to_table((regexp_matches(url, '\?(.*)'))[1], '&'), '=', 1) as key,
  split_part(regexp_split_to_table((regexp_matches(url, '\?(.*)'))[1], '&'), '=', 2) as value
from ad;

did you have index for timestamp? Performance questions should include EXPLAIN ANALYZE and some information about table size, index, current time performance, desire time, etc. Slow is a relative term and we need a real value to compare. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 16, 2016 at 14:57
At the moment around 1.75M rows only, but it will fill quickly to tens or hundreds of millions. Oops I didn't realise I pasted it with the order by and limit rows, didn't mean to, sorry. I edited the question to include only the neccessary things. — Josef Joe Samanek
– Josef Joe Samanek, Commented Dec 16, 2016 at 15:05
Still without the EXPLAIN ANALYZE we cant know where is the slow part of the process. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 16, 2016 at 15:56
So a FULL SCAN on 1.75 M rows? How fast you expect that be? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 16, 2016 at 18:40

9000 · Accepted Answer · 2016-12-16 15:55:21Z

1

You do two regexp matches for the same URL, and then two splits of the result.

I'd suggest using a simpler function to find the position of the first ? in the URL, e.g. substring(url, position('?' in url)), then you likely can use regexp_split_to_array to do the splitting only once.

This can happen in a common table expression or a subquery; then you can access the array results by index to return.

This, of course, only makes sense if the query is dominated by the processing time, and not filtering and fetching time. Optimization without profiling is a waste of time.

answered Dec 16, 2016 at 15:55

9000

41k9 gold badges69 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql parse url parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related