Converting SalesForce IDs for Performance Reasons The 2019 Stack Overflow Developer Survey...
University's motivation for having tenure-track positions
What aspect of planet Earth must be changed to prevent the industrial revolution?
system() function string length limit
Would an alien lifeform be able to achieve space travel if lacking in vision?
In horse breeding, what is the female equivalent of putting a horse out "to stud"?
Single author papers against my advisor's will?
What information about me do stores get via my credit card?
First use of “packing” as in carrying a gun
Wolves and sheep
Working through the single responsibility principle (SRP) in Python when calls are expensive
The following signatures were invalid: EXPKEYSIG 1397BC53640DB551
Match Roman Numerals
Python - Fishing Simulator
What are these Gizmos at Izaña Atmospheric Research Center in Spain?
Keeping a retro style to sci-fi spaceships?
Can the prologue be the backstory of your main character?
Windows 10: How to Lock (not sleep) laptop on lid close?
How can I protect witches in combat who wear limited clothing?
How many people can fit inside Mordenkainen's Magnificent Mansion?
Didn't get enough time to take a Coding Test - what to do now?
Problems with Ubuntu mount /tmp
Relations between two reciprocal partial derivatives?
Make it rain characters
Is this wall load bearing? Blueprints and photos attached
Converting SalesForce IDs for Performance Reasons
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Reasons for occasionally slow queries?How can I speed up a Postgres query containing lots of Joins with an ILIKE conditionpostgres explain plan with giant gaps between operationsHow to optimize multiple ORDER BYs?Excluding rows by lots of idspostgresql 9.2 hash join issueSorting killing my postgresql queryHow to make DISTINCT ON faster in PostgreSQL?How to index two tables for JOINed query optimisationPerformance difference in accessing differrent columns in a Postgres Table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom: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
decimaltype 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 but doesn't really matter to me, unless someone knows an easy way to keep them ordered that doesn't hamper performance
postgresql performance query-performance identity
asked 11 mins ago
Alex WAlex W
1114
add a comment |
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
decimaltype 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 but doesn't really matter to me, unless someone knows an easy way to keep them ordered that doesn't hamper performance
postgresql performance query-performance identity
asked 11 mins ago
Alex WAlex W
1114
add a comment |
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
decimaltype 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 but doesn't really matter to me, unless someone knows an easy way to keep them ordered that doesn't hamper performance
postgresql performance query-performance identity
asked 11 mins ago
Alex WAlex W
1114
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
decimaltype 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 but doesn't really matter to me, unless someone knows an easy way to keep them ordered that doesn't hamper performance
postgresql performance query-performance identity
postgresql performance query-performance identity
asked 11 mins ago
Alex WAlex W
1114
asked 11 mins ago
Alex WAlex W
1114
edited 3 mins ago
Alex W
asked 11 mins ago
Alex WAlex W
1114
asked 11 mins ago
Alex WAlex W
1114
asked 11 mins ago
Alex WAlex W
1114
1114
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234739%2fconverting-salesforce-ids-for-performance-reasons%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234739%2fconverting-salesforce-ids-for-performance-reasons%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
