0

I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys and add them to dedicated columns in my table.

Some notes on the data:

  1. The keys I am interested in are connected, as in keyB relates to keyA;
  2. in some cases keyB or keyA may not exist
  3. If keyB doesn't exist, but keyA is something specific, then I can set the value for keyB anyway.

I have a solution that does what I want ([db-fiddle]), but it is painfully slow (9.6 hours) and I can't help think that there must be a better way as I've not been in this DB game long.

For info, The table has ~8.2M rows and is hosted on AWS RDS on a t3.large DB (virtual CPUs = 2, Memory = 8.0GB).

Some pointers on where I can improve this are much appreciated.

2
  • I think you could make your life a lot easier if tags was a jsonb column. Can you change that?
    – user1822
    Aug 13, 2020 at 13:57
  • Thanks for your answers. I could convert it to jsonb yes, would this just be better to allow me to use keys or is there something else I have missed?
    – SteveO29
    Aug 15, 2020 at 21:35

2 Answers 2

0

You only need a single INSERT and a single UPDATE for the main table. The whole default detection and populating of the "variables" (columns) can be done while inserting the data:

INSERT INTO main_tags (id, tags, variablea, variableb)
select id, tags, var_a, 
       case 
          when var_b is null and 'valA' = any(tags)  then 'valB_default'
          when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
          else var_b
       end as var_b
from (
  SELECT id, tags, 
         tags[array_position(tags, 'keyA') + 1] as var_a, 
         tags[array_position(tags, 'keyB') + 1] as var_b
  FROM (
    SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
    FROM main
  ) as b 
) x  
WHERE tags && array['keyA','keyB'];

The final update can also handle the "unknown" value directly, no need to run three updates:

UPDATE main e
  SET variableA = coalesce(et.variableA, 'unknown'),
      variableB = coalesce(et.variableB, 'unknown')
FROM main_tags et
WHERE e.id = et.id;

One way to speed that up, is to create an index on main_tags (id)


You can actually get rid of the temp table completely and do everything in a single statement. This might or might not be faster, but I think it's worth trying:

UPDATE main e
  SET variableA = coalesce(et.var_a, 'unknown'),
      variableB = coalesce(et.var_b, 'unknown')
FROM (
  select id, tags, 
         var_a, 
         case 
            when var_b is null and 'valA' = any(tags)  then 'valB_default'
            when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
            else var_b
         end as var_b
  from (
    SELECT id, 
           tags, 
           tags[array_position(tags, 'keyA') + 1] as var_a, 
           tags[array_position(tags, 'keyB') + 1] as var_b
    FROM (
      SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
      FROM main
    ) as b 
  ) x  
  WHERE tags && array['keyA','keyB']
) as et
WHERE e.id = et.id;
1
  • FYI, A quick test showed that both methods seemed to be very similar in speed. Thanks for the answers
    – SteveO29
    Aug 16, 2020 at 9:20
0

If your data examples are representative (as far as length of the "tags" field), there is only a few minutes worth of string processing here even at 10e6 rows. The rest of the time is spent making repeated unnecessary passes over the data, and at least one is probably done in a horribly inefficient IO pattern.

Why are you loading data that requires so much processing in the first place, rather than processing before or during the load? It looks like every row can be processed individually, so you should be able to process them in python or perl and then stream the processed data into the database.

Why are you using a t3 instance? The t3 class has performance problems by design. If you care about performance (and apparently you do or you wouldn't be asking the question), you shouldn't use them.

Why do you care about performance anyway? It may have been slow, but it is (apparently) already done. Are you going to have to keep doing this on an ongoing basis? If so, will it happen from a clean table each time, or will you be adding more rows to a populated table, then only needing to process the new rows?

Is there a reason you need to update "main"? Creating new table main_new as the result of a query between "main" and main_tags (or inserting into main_real based on that query) should be much more efficient than trying to do an in-place update. This is the classic "staging table" design.

Finally, PostgreSQL does a poor job of planning bulk UPDATE...FROM. It plans the UPDATE just as if it were a SELECT, not estimating the cost of the UPDATE itself. This ignores the fact that some plans would update the table in sequential order, while others would jump all over around the table creating lots of slow IO.

If you did an EXPLAIN on your UPDATE...FROM, you would probably get something like this:

                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Update on main e  (cost=427663.40..953462.12 rows=8498274 width=119)
   ->  Hash Join  (cost=427663.40..953462.12 rows=8498274 width=119)
         Hash Cond: (et.id = e.id)
         ->  Seq Scan on main_tags et  (cost=0.00..204676.74 rows=8498274 width=74)
         ->  Hash  (cost=238341.62..238341.62 rows=8502862 width=49)
               ->  Seq Scan on main e  (cost=0.00..238341.62 rows=8502862 width=49)

This is probably fine as long as the hash table is held in memory, but if work_mem is low enough that it spills to disk you will doing a lot of random IO. If you just set enable_hashjoin=off you will probably get a much IO friendlier plan (although that could depend on how well clustered your table is on "id")

2
  • I am doing all the processing on the DB as I assumed that would be faster than pulling it into python, processing, and then posting back. Do you think that is a bad assumption? I'm using a t3 instance as I thought that was the best machine for the job (a web app that users access through our front end to run queries - this is a geospatial routing DB. so a user submits a routing request and then backend uses python/this DB to provide the answer) - what is the issue with t3s? Can you recommend a better suited machine? I care about speed as I want to roll this DB out to multiple countries.
    – SteveO29
    Aug 15, 2020 at 21:42
  • I tried turning hashjoin off but the query plan increased time ~10x on a sample DB. I have the 'main' DB that contains a lot of other geospatial information so ideally I would update that rather than create a new DB, but could also move everything to a new table if that is faster. I'll check it out,. Thanks
    – SteveO29
    Aug 15, 2020 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.