用例 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