0

I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so:

001n000000UELLJAA5

Where each digit is a base-62 value denoted by:

a-z (26 values, case-sensitive)
A-Z (26 values, case-sensitive)
0-9 (10 values)

26+26+10 = a total of 62 values per digit

I want to convert these into a format that can be efficiently indexed by Postgres for thousands of rows.

I have gotten this far:

select id, array_to_string(array_agg(ascii),'','0')::decimal
FROM (
       SELECT id, ascii(regexp_split_to_table(id, '')) from example_schema.example_table
     ) x
group by id

but it seems super inefficient and also may be hard to reverse in some cases because there's no delimiter between the codes.

  • Is using a decimal type as an ID/identity efficient?
  • Is there an easier way to convert these to a numeric value that can still be used as a primary key and is a reversible operation if needed?

Here's the query plan:

GroupAggregate  (cost=109862.92..130267.92 rows=742000 width=51)
  Group Key: account.id
  ->  Sort  (cost=109862.92..111717.92 rows=742000 width=23)
        Sort Key: account.id
        ->  Result  (cost=0.00..14875.99 rows=742000 width=23)
              ->  ProjectSet  (cost=0.00..3745.99 rows=742000 width=51)
                    ->  Seq Scan on account  (cost=0.00..30.42 rows=742 width=19)

Example output:

001n000000ShgGbAAJ  746565987110310483484848484848110494848
001n000000SIZE7AAP  4848491104848484848488373906955656580
001n000000Sj3NCAAZ  48906565677851106834848484848481104948
001n000000SJK1sAAH  48484911048484848484883747549115656572

They seem out of order which will cause issues with reversing the operation

Improve this question
3
  • Postgres can efficiently index text or (varchar) columns. What kind of query are you trying to improve on original ID values? For a small database with just "thousands" of rows I wouldn't bother to begin with (with hundreds of millions maybe) - especially as you can't remove the original IDs (I assume)
    – user1822
    Apr 14, 2019 at 11:00
  • @a_horse_with_no_name For the data I'm pulling from SalesForce, the rows will likely only reach the hundreds of thousands but there are other systems that already have millions of rows and counting that I might need to do joins with, etc. I've heard that indexes on numeric columns are faster because they can take advantage of b-trees?
    – Alex W
    Apr 15, 2019 at 13:07
  • Indexes on varchar columns are also implemented as B-Trees. While there is a performance difference between joining on numbers and joining on varchars, I doubt that you will actually notice that difference. I wouldn't bother duplicating information unless you know you have a performance problem and you know that it's related to the varchar primary key values. You can alleviate the impact of comparing strings a little by defining the varchar columns with collate "C" which makes comparing the strings faster
    – user1822
    Apr 15, 2019 at 13:10

0

Reset to default

Your Answer

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