Postgresql – Optimal query for comparing table column vs. summary column

performancepostgresqlquery-performance

What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table?

For example, I have an account table and a task table. Below is the query I'm currently executing to compare a stamped value on the account table to actual counts from the task table.

SELECT
    account.sfid,
    account.lastactivitydate,
    account.sales_activities_last_30_days__c AS expectedcount,
    (
        SELECT COUNT(*)
        FROM salesforce.task
        WHERE task.accountid = account.sfid AND
              task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
    ) AS actualcount
FROM salesforce.account
;

This is a naive attempt to get the data I want, and I'd appreciate any suggestions on optimizing this query to get results like what's shown in the table below.

sfid lastactivitydate expectedcount actualcount
---- ---------------- ------------- -----------
abc  2019-11-30       3             3
def  2019-11-27       5             4

Best Answer

Test

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COALESCE(subquery.cnt, 0) AS actualcount
FROM salesforce.account
LEFT JOIN ( SELECT task.accountid, COUNT(*) cnt
            FROM salesforce.task
            WHERE task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
            GROUP BY task.accountid
          ) AS subquery ON subquery.accountid = account.sfid;

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COUNT(task.accountid) AS actualcount
FROM salesforce.account
LEFT JOIN salesforce.task 
    ON task.accountid = account.sfid
   AND task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY account.sfid,
         account.lastactivitydate,
         account.sales_activities_last_30_days__c;

Related Solutions

MySQL – Should UUID be Used Alongside ID

UUIDs are a performance disaster for very large tables. (200K rows is not "very large".)

Your #3 is really bad when the CHARCTER SET is utf8 -- CHAR(36) occupies 108 bytes! Update: There are ROW_FORMATs for which this will stay 36.

UUIDs (GUIDs) are very "random". Using them as either a UNIQUE or a PRIMARY key on large tables is very inefficient. This is because of having to jump around the table/index each time you INSERT a new UUID or SELECT by UUID. When the table/index is too large to fit in cache (see innodb_buffer_pool_size, which must be smaller than RAM, typically 70%), the 'next' UUID may not be cached, hence a slow disk hit. When the table/index is 20 times as big as the cache, only 1/20th (5%) of hits are cached -- you are I/O-bound. Generalization: The inefficiency applies to any "random" access -- UUID / MD5 / RAND() / etc

So, don't use UUIDs unless either

you have "small" tables, or
you really need them because of generating unique ids from different places (and have not figured out another way to do it).

More on UUIDs: http://mysql.rjweb.org/doc.php/uuid (It includes functions for converting between standard 36-char UUIDs and BINARY(16).) Update: MySQL 8.0 has a builtin function for such.

Having both a UNIQUE AUTO_INCREMENT and a UNIQUE UUID in the same table is a waste.

When an INSERT occurs, all unique/primary keys must be checked for duplicates.
Either unique key is sufficient for InnoDB's requirement of having a PRIMARY KEY.
BINARY(16) (16 bytes) is somewhat bulky (an argument against making it the PK), but not that bad.
The bulkiness matters when you have secondary keys. InnoDB silently tacks the PK onto the end of each secondary key. The main lesson here is to minimize the number of secondary keys, especially for very large tables. Elaboration: For one secondary key, the bulkiness debate usually ends in a draw. For 2 or more secondary keys, a fatter PK usually leads to a bigger disk footprint for the table including its indexes.

For comparision: INT UNSIGNED is 4 bytes with range of 0..4 billion. BIGINT is 8 bytes.

Italics Updates/etc were added Sep, 2017; nothing critical changed.

Addressing Ivan's answer and the link he provided.

Yes, there are benefits of a distributed id-generator. But...
There are other ways to avoid the delay of a centralized ID server -- Dispense 100 consecutive ids at a time to the client. Or use a client-generated concatenation of client_id and time.
If the entire index is cached in RAM, then much of my argument is moot. But once it spills to disk, performance suffers severely. His article does briefly point out that UUIDv4 is slower than UUIDv1, but fails to point out that the bits need rearranging to achieve the benefit.
I agree with squeezing a 36-byte UUID into a 16-byte BINARY. MySQL 8 even has a function to do such. Furthermore, it (and my blog) rearrange the bits so that UUIDv1 has the temporal characteristic of an auto_increment.
Sharding, I contend, is not normally done against auto_increment ids, so switching to UUIDs does not buy anything.

Optimal design for a large database

This is a classic situation where you use a "joining table".

You will need three tables.

Person
Skill
Person_Skill

(note that table names are singular - this is a preference of mine, not a rule).

Person
ID integer PRIMARY KEY
<other person details - name, address, phone &c.>
KEY name (and possibly phone?)

Skill
ID integer PRIMARY KEY
<other skill details - name, description, necessary qualification &c.>
KEY name (maybe not even necessary if there are only 50 skills?)    

Person_Skill
Person_ID integer FOREIGN KEY REFERENCES Person(ID)
Skill_ID integer FOREIGN KEY REFERENCES Skill(ID)
Unique Index on (Person_ID, Skill_ID) (to avoid duplicates)
KEY Skill_ID

This way you don't store the skill name, description &c. for every person who has that skill, but just once and then refer to it in the Person_Skill table by its ID - this reduces space and memory requirements and is less error prone (you only store the Skill datum once - one of the core features of an RDBMS under Codd's rules).

That is the way I would do this.

Best Answer

Related Solutions

MySQL – Should UUID be Used Alongside ID

Optimal design for a large database

Related Question