Postgresql – Select records and related entity count

group bypostgresql

Given the following select that will count related entities for every record

SELECT MAIN.*, SUB.app_count
FROM my_table_view MAIN
     LEFT JOIN (SELECT MAIN2.p_key,
                       count(DISTINCT T3.app_id) AS app_count
                FROM my_table MAIN2
                         JOIN T1 ...
                         LEFT JOIN T2 ...
                         LEFT JOIN T3 ...
                GROUP BY MAIN2.p_key) SUB ON MAIN.p_key = SUB.p_key
WHERE ...

Trying to simplify it, came up with the following :

SELECT MAIN.p_key, count(DISTINCT T3.app_id) AS app_count -- select MAIN.* errors out - missing GROUP BY
FROM my_table_view MAIN
     LEFT JOIN T1 ON ...
     LEFT JOIN T2 ON ...
     LEFT JOIN T3 ON ...
WHERE ...
GROUP BY MAIN.p_key

How to select all data from the MAIN table without adding a bunch of GROUP BYs ?
Would also appreciate performance hints.

Edit

I think this is related
Postgres GROUP BY id works on table directly, but not on identical view

Best Answer

If p_key is the primary key of my_table, it should not be necessary to add the other columns, unless you want the statement to comply with the SQL standard.

Related Solutions

Should I group by and then join, or join and then group by

The optimizer always tries to reduce the amount of data as quickly as possible. If not, your statistics might not be good.

Your plan 1 shows that less rows are processed this is good. The optimizer was able to reduce the amount of data more quickly. The numbers might not be exactly true but it gives you an idea based on the optimizer statistics.

Plan 1:

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     1 |    79 |     7  (15)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                         |      |     1 |    79 |     7  (15)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                         |      |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                        |      |     1 |    79 |     6   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                       |      |     1 |    63 |     5   (0)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| t1   |     1 |    22 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                 | t1pk |     1 |       |     2   (0)| 00:00:01 |       |       |
|   7 |      PARTITION RANGE ITERATOR          |      |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID| t3   |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |        INDEX RANGE SCAN                | t3fk |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |     INDEX UNIQUE SCAN                  | t2pk |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | t2   |     1 |    16 |     1   (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------

It shows that Oracle did a HASH GROUP BY while plan 2 did a SORT GROUP BY.

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |      |     1 |   311 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                            |      |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                           |      |     1 |   311 |     7  (15)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |      |     1 |   253 |     6  (17)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | t1   |     1 |   164 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                    | t1pk |     1 |       |     2   (0)| 00:00:01 |       |       |
|   6 |     VIEW PUSHED PREDICATE                |      |     1 |    89 |     3  (34)| 00:00:01 |       |       |
|   7 |      SORT GROUP BY                       |      |     1 |    41 |     3  (34)| 00:00:01 |       |       |
|*  8 |       FILTER                             |      |       |       |            |          |       |       |
|   9 |        PARTITION RANGE SINGLE            |      |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID| t3   |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 11 |          INDEX RANGE SCAN                | t3fk |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 12 |    INDEX UNIQUE SCAN                     | t2pk |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 13 |   TABLE ACCESS BY GLOBAL INDEX ROWID     | t2   |     1 |    58 |     1   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------

Conclusion: In most cases I would prefer a HASH GROUP BY. So in plan 1 Oracle does a better the GROUP BY much later with much less data. 2 reasons why plan 1 is better.

I assume that this is not the only SQL statement which will be using this join constellation. You will have to decide for each SQL which way is the best.

If you have a SQL similar to the following, the with clause can be much better:

with t3g as ( -- as in 't3 grouped'
    select id2, count(1) t3count
    from t3
    where id = :1 -- new line !!!!!!!!
    group by id2
)
select t1.id t1id, t2id, t3count
from t1 join t2  on (t1.p = t2.p  and t1.id = t2.id1)
        join t3g on (t2.p = t3g.p and t2.id = id2)

MySQL – How to Find Employees Who Have Switched Jobs at Least Twice

Your Query should be like:

SELECT * FROM job_history INNER JOIN employees 
ON job_history.employee_id = employees.employee_id
WHERE job_history.employee_ID IN
    (SELECT employee_id
         FROM job_history
         GROUP BY employee_id
         HAVING COUNT(employee_id) >= 2
    );

so you missed the alias before the id "WHERE job_history.employee_ID IN..", and the where in change it to what above...

Edit

Best Answer

Related Solutions

Should I group by and then join, or join and then group by

MySQL – How to Find Employees Who Have Switched Jobs at Least Twice

Related Question