使用案例 2 — 資料類型不符

根據資料選擇適當的資料類型，有助於在儲存大小和效能之間取得最佳平衡。

下列範例查詢會使用資pnr_number料行來連接兩個資料表。該pnr_number列在不同的表中具有不同的數據類型。

EXPLAIN ANALYZE UPDATE perf_user.RNR_SEGMENT_PAX x SET ARC_EXPIRY_DATE = y.ARC_EXPIRY_DATE
       FROM (SELECT AIRLINE_IATA_CODE, PNR_NUMBER, ARC_EXPIRY_DATE, 0+row_num ROW_NUM
               FROM perf_user.RNR_EXPIRY_DATE 
              WHERE airline_iata_code = 'XX' 
                AND row_num BETWEEN (1*5000)+0 AND (1+1)*5000) y
         WHERE  x.airline_iata_code = y.airline_iata_code 
             AND  x.PNR_NUMBER =y.PNR_NUMBER;

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

Update on rnr_segment_pax x  (cost=290.97..1104986.32 rows=15515 width=460) (actual time=14574.118..14574.120 rows=0 loops=1)
  ->  Hash Join  (cost=290.97..1104986.32 rows=15515 width=460) (actual time=16.967..14101.983 rows=11953 loops=1)
       Hash Cond: ((x.pnr_number)::text = (rnr_expiry_date.pnr_number)::text)
        ->  Seq Scan on rnr_segment_pax x  (cost=0.00..954539.00 rows=40000320 width=446) (actual time=0.011..9702.989 rows=40000000 loops=1)
              Filter: ((airline_iata_code)::bpchar = 'XX'::bpchar)
        ->  Hash  (cost=225.37..225.37 rows=5248 width=24) (actual time=16.540..16.541 rows=5001 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 338kB
              ->  Index Scan using rnr_expiry_date_idx1 on rnr_expiry_date  (cost=0.29..225.37 rows=5248 width=24) (actual time=3.102..15.331 rows=5001 loops=1)
                    Index Cond: ((row_num >= 5000) AND (row_num <= 10000))
                    Filter: (airline_iata_code = 'XX'::bpchar)
Planning Time: 4.445 ms
Execution Time: 14574.322 ms

執行時EXPLAIN ANALYZE，即使聯結中使用的資料行具有索引，規劃工具也會使用序列掃描rnr_segment_pax而非索引掃描。規劃工具不使用索引掃描，因為聯結中使用的資料行長度不同。

改變表列，使聯接條件中涉及的兩個表的數據類型保持相同，然後分析表：

alter table perf_user.rnr_expiry_date alter column airline_iata_code type character varying(6) ;

analyze perf_user.rnr_expiry_date;

現在，這些表在連接條件中使用的兩個列具有相同的長度。

再次執行 EXPLAIN ANALYZE。供需規劃員會執行索引掃描，大幅改善查詢效能。

Update on rnr_segment_pax x  (cost=0.86..59733.09 rows=14637 width=460) (actual time=416.653..416.654 rows=0 loops=1)
  ->  Nested Loop  (cost=0.86..59733.09 rows=14637 width=460) (actual time=0.103..91.106 rows=11953 loops=1)
        ->  Index Scan using rnr_expiry_date_idx1 on rnr_expiry_date  (cost=0.29..212.69 rows=4951 width=24) (actual time=0.025..3.023 rows=5001 loops=1)
              Index Cond: ((row_num >= 5000) AND (row_num <= 10000))
              Filter: ((airline_iata_code)::text = 'XX'::text)
        ->  Index Scan using rnr_segment_pax_pk on rnr_segment_pax x  (cost=0.56..11.99 rows=3 width=446) (actual time=0.014..0.016 rows=2 loops=5001)
              Index Cond: (((airline_iata_code)::text = 'XX'::text) AND ((pnr_number)::text = (rnr_expiry_date.pnr_number)::text))
Planning Time: 0.310 ms
Execution Time: 416.696 ms