A recent post on a naver cafe prompted me to write this article:

https://cafe.naver.com/dbian/5442 

The poster gave a quiz about how we can reduce the number of block IOs of a specific query. And I wondered how query performance will be in PostgreSQL. So I have copied the test scripts (with minor cosmetic changes) and done the test in PostgreSQL.

We start with two tables:

CREATE TABLE 주문
(
  주문ID        VARCHAR(40) NOT NULL
  ,주문일시     DATE           NULL
  ,주문요청자ID VARCHAR(40)  NULL
);
ALTER TABLE 주문 ADD CONSTRAINT PK_주문 PRIMARY KEY(주문ID) ;
TRUNCATE TABLE 주문;

CREATE TABLE 주문처리내역
(
   주문ID VARCHAR(40) NOT NULL
   ,주문순번 NUMERIC(9) NOT NULL
   ,주문처리일시 DATE NULL
   ,주문처리자ID VARCHAR(40) NULL
);
ALTER TABLE 주문처리내역 ADD CONSTRAINT PK_주문처리내역 PRIMARY KEY(주문ID,주문순번);
TRUNCATE TABLE 주문처리내역;

주문 is a parent table and 주문처리내역 is a child table.

Let's load some data into these tables.

INSERT INTO 주문(주문ID,주문일시,주문요청자ID)
WITH R_DT AS (
      SELECT generate_series('20220101'::timestamp, '20220131'::timestamp, interval '1 day') AS DT
      ),
  R_ORD AS (
      SELECT i ONO FROM generate_series(1,100) a(i)   
      ),
  M_CUS AS (
      SELECT 'CUS_'||i::varchar AS CUS_ID FROM generate_series(1, 100) a(i)
           )
SELECT  TO_CHAR(T1.DT,'YYYYMMDD')||'_'||(ROW_NUMBER() OVER(PARTITION BY T1.DT ORDER BY T0.ONO ASC,T2.CUS_ID ASC),5,'0')::varchar 주문ID
        ,T1.DT 주문일시
        ,T2.CUS_ID 주문요청자ID
FROM    R_ORD T0
        ,R_DT T1
        ,M_CUS T2;
SELECT pg_relation_size('주문');

INSERT INTO 주문처리내역(주문ID,주문순번,주문처리일시,주문처리자ID)
SELECT  T1.주문ID
        ,T2.RNO 주문순번
        ,T1.주문일시 주문처리일시
        ,T1.주문요청자ID 주문처리자ID
FROM    주문 T1
        ,(SELECT i RNO FROM generate_series(1,30) a(i)) T2;

SELECT pg_relation_size('주문처리내역');

I have updated some values of a column in the 주문 table.

--3100 rows updated
UPDATE  주문
SET     주문요청자ID = NULL
WHERE   주문요청자ID = 'CUS_1';

I have disabled parallelism for the purpose of simplicity.

SET MAX_PARALLEL_WORKERS_PER_GATHER = 0;

The following is the query we have to investigate.

SELECT  T1.주문ID, T1.주문일시, COALESCE(T1.주문요청자ID,T2.주문처리자ID) 주문요청자ID
  FROM    주문  T1 LEFT JOIN 주문처리내역  T2
      ON t1.주문ID = t2.주문ID
    AND T2.주문순번 = 1
 WHERE T1.주문일시 >= TO_DATE('20220104','YYYYMMDD')
      AND T1.주문일시 < TO_DATE('20220105','YYYYMMDD')
ORDER BY T1.주문일시 ASC, T1.주문ID ASC;

Here, the poster asked readers about how we can improve above query performance. To be honest, I couldn't find any inefficiency in the above query even after reading the execution plan. The following is the output I got when I ran the EXPLAIN command with the (analyze, buffers, costs off) option.

Sort (actual time=149.187..149.509 rows=10000 loops=1)
  Sort Key: t1."주문일시", t1."주문id"
  Sort Method: quicksort  Memory: 1166kB
  Buffers: shared hit=52300
  ->  Nested Loop Left Join (actual time=5.921..144.637 rows=10000 loops=1)
        Buffers: shared hit=52300
        ->  Seq Scan on "주문" t1 (actual time=5.900..110.233 rows=10000 loops=1)
              Filter: (("주문일시" >= to_date('20220104'::text, 'YYYYMMDD'::text)) AND ("주문일시" < to_date('20220105'::text, 'YYYYMMDD'::text)))
              Rows Removed by Filter: 300000
              Buffers: shared hit=2300
        ->  Index Scan using "pk_주문처리내역" on "주문처리내역" t2 (actual time=0.003..0.003 rows=1 loops=10000)
              Index Cond: ((("주문id")::text = (t1."주문id")::text) AND ("주문순번" = '1'::numeric))
              Buffers: shared hit=50000
Planning:
  Buffers: shared hit=20
Planning Time: 0.268 ms
Execution Time: 149.770 ms

The following is how to read the plan.

1. The first thing done is a Seq Scan on "주문". (actual time=5.900..110.233 rows=10000 loops=1) means that the Seq Scan was executed 1 time (the loops value), that it returned 10,000 rows, and that the actual time was 110.233 ms.  Buffers: shared hit=2300 means that while doing a Seq Scan, 2300 data blocks were found in the shared buffer. You can find the number of blocks the table 주문 has with a query against pg_class like so:

  SELECT relpages FROM pg_class WHERE relname = '주문';

  relpages

 ------------

  2280

I can not explain why it accessed 2300 buffers, not 2280.

The results of the Seq Scan are passed up to a Nested Loop Left Join node.

2. For each row from Seq Scan on "주문", Nested Loop Left Join node calls Index Scan.

3. Index Scan probes the pk_주문처리내역 index to find a match with the Index Cond. When Index Scan finds a match, it passes it up to the Nested Loop Left Join node.

4. The Nested Loop Left Join node calls Index Scan again with its second row.

5. It iterates the process of the operation 2 to 4 until the Nested Loop Left Join exhausts 10,000 rows, which means that Index Scan is executed 10,000 times. We can infer that it took about 30 ms doing the Index Scan (0.003 * 10,000 = 30).

6. While doing the Index Scan 10,000 times, PostgreSQL had to access 50,000 blocks, which is a relatively high number compared to 2300 blocks it had to access while doing a Seq Scan.

So after reading the execution plan, can you spot any inefficiency? Instinctually I thought that the join method might not be optimal and I tried to force the planner to do a hash join. But it resulted in slower elapsed time. So I had to see the explanation of the poster. 

The drawback of the above execution plan is that it is accessing the index 10,000 times. When we take a close look at the COALESCE(T1.주문요청자ID,T2.주문처리자ID) function, we can infer that we don't have to access the T2 table when T1.주문요청자ID is not null. However, the execution plan shows that PostgreSQL is accessing the index of the T2 table regardless of the T1.주문요청자ID value.

So the trick here is to rewrite the query using a scalar subquery. The scalar subquery is executed after the main query is executed.

SELECT  T1.주문ID ,T1.주문일시
        ,COALESCE(T1.주문요청자ID
               ,(SELECT X.주문처리자ID
                 FROM 주문처리내역 X
                 WHERE X.주문ID = T1.주문ID AND X.주문순번 = 1)
             ) 주문요청자ID
FROM    주문 T1
WHERE   T1.주문일시 >= TO_DATE('20220104','YYYYMMDD')
AND     T1.주문일시 < TO_DATE('20220105','YYYYMMDD')
ORDER BY T1.주문일시 ASC, T1.주문ID ASC;

Sort (actual time=106.273..106.577 rows=10000 loops=1)
  Sort Key: t1."주문일시", t1."주문id"
  Sort Method: quicksort  Memory: 1166kB
  Buffers: shared hit=2800
  ->  Seq Scan on "주문" t1 (actual time=6.658..102.758 rows=10000 loops=1)
        Filter: (("주문일시" >= to_date('20220104'::text, 'YYYYMMDD'::text)) AND ("주문일시" < to_date('20220105'::text, 'YYYYMMDD'::text)))
        Rows Removed by Filter: 300000
        Buffers: shared hit=2800
        SubPlan 1
          ->  Index Scan using "pk_주문처리내역" on "주문처리내역" x (actual time=0.004..0.004 rows=1 loops=100)
                Index Cond: ((("주문id")::text = (t1."주문id")::text) AND ("주문순번" = '1'::numeric))
                Buffers: shared hit=500
Planning Time: 0.213 ms
Execution Time: 106.833 ms 

From the execution plan, we can deduce the following:

- The scalar subquery was executed 100 times (loops=100).

- While accessing the primary key index, it had to access 5 blocks.

By transforming the original SQL statement into using a scalar subquery we could decrease the block I/O from 52300 to 2800. And the elapsed time dropped from 149 ms to 106 ms.

Conclusion

When we join two tables using the LEFT JOIN clause,  consider transforming the query into using a scalar subquery or vice versa.