PostgreSQL优化器案例之-orderbylimit索引选择问题

SQL> create index idx_tbl_1 on tbl(id);Index > create index idx_tbl_2 on tbl(c1,c2,c3,c4);Index > set linesize 512SQL> set pagesize 50000SQL> set autotrace on;SQL> exec DBMS__TABLE_STATS('JIUDU','TBL');

PL/SQL procedure successfully > select * from (select * from tbl where c1=200 and c2=200 order by id) t where rownum<10; ID C1 C2 C3 C4---------- ---------- ---------- ---------- ---------- 10000001 200 200 200 200 10000002 200 200 200 200 10000003 200 200 200 200 10000004 200 200 200 200 10000005 200 200 200 200 10000006 200 200 200 200 10000007 200 200 200 200 10000008 200 200 200 200 10000009 200 200 200 2009 rows ion Plan----------------------------------------------------------Plan hash value: 745043579----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 585 | | 10253 (2)| 00:02:04 ||* 1 | COUNT STOPKEY | | | | | | || 2 | VIEW | | 84875 | 5387K| | 10253 (2)| 00:02:04 ||* 3 | SORT ORDER BY STOPKEY| | 84875 | 1491K| 2672K| 10253 (2)| 00:02:04 ||* 4 | TABLE ACCESS FULL | TBL | 84875 | 1491K| | 9767 (2)| 00:01:58 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<10) 3 - filter(ROWNUM<10) 4 - filter("C1"=200 AND "C2"=200)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 34868 consistent gets 0 physical reads 0 redo size 937 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client

2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processedSQL> select * from (select * from tbl where c1=1 and c2=1 order by id) t where rownum<10; ID C1 C2 C3 C4---------- ---------- ---------- ---------- ---------- 9697 1 1 78 39 20586 1 1 81 71 27820 1 1 33 64 44324 1 1 26 27 47079 1 1 3 5 64669 1 1 13 49 73715 1 1 20 74 80903 1 1 96 25 98368 1 1 59 99 rows ion Plan----------------------------------------------------------Plan hash value: 447312937--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 585 | 641 (1)| 00:00:08 ||* 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 704 | 45760 | 641 (1)| 00:00:08 ||* 3 | SORT ORDER BY STOPKEY | | 704 | 12672 | 641 (1)| 00:00:08 || 4 | TABLE ACCESS BY INDEX ROWID| TBL | 704 | 12672 | 640 (0)| 00:00:08 ||* 5 | INDEX RANGE SCAN | IDX_TBL_2 | 704 | | 5 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<10) 3 - filter(ROWNUM<10) 5 - access("C1"=1 AND "C2"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 1072 consistent gets 11 physical reads 0 redo size 969 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processedSQL> create index idx_tbl_3 on tbl(c1,c2,id);Index > select * from (select * from tbl where c1=200 and c2 between 100 and 300 order by id) t where rownum < 10; ID C1 C2 C3 C4

ID C1 C2 C3 C4---------- ---------- ---------- ---------- ---------- 10000001 200 200 200 200 10000002 200 200 200 200 10000003 200 200 200 200 10000004 200 200 200 200 10000005 200 200 200 200 10000006 200 200 200 200 10000007 200 200 200 200 10000008 200 200 200 200 10000009 200 200 200 2009 rows ion Plan----------------------------------------------------------Plan hash value: 745043579----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 585 | | 10253 (2)| 00:02:04 ||* 1 | COUNT STOPKEY | | | | | | || 2 | VIEW | | 84875 | 5387K| | 10253 (2)| 00:02:04 ||* 3 | SORT ORDER BY STOPKEY| | 84875 | 1491K| 2672K| 10253 (2)| 00:02:04 ||* 4 | TABLE ACCESS FULL | TBL | 84875 | 1491K| | 9767 (2)| 00:01:58 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<10) 3 - filter(ROWNUM<10) 4 - filter("C1"=200 AND "C2">=100 AND "C2"<=300)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 34868 consistent gets 0 physical reads 0 redo size 937 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processedSQL> select * from (select * from tbl where c1=200 and c2 =200 order by id) t where rownum < 10; ID C1 C2 C3 C4---------- ---------- ---------- ---------- ---------- 10000001 200 200 200 200 10000002 200 200 200 200 10000003 200 200 200 200 10000004 200 200 200 200 10000005 200 200 200 200 10000006 200 200 200 200 10000007 200 200 200 200 10000008 200 200 200 200 10000009 200 200 200 2009 rows selected.

select * from tbl where c1=200 and c2 between 100 and 300 order by id limit 10;

增加索引

(c1,id) -- 索引扫描, filter c2

已有

(c1,c2) -- 索引扫描, sort id

(id) -- 索引扫描, filter c1,c2

select * from tbl where c1=200 and c2 =200 order by id limit 10;

增加索引

(c1,c2,id) -- 索引扫描

已有

(c1,c2) -- 索引扫描, sort id

(id) -- 索引扫描, filter c1,c2

参考