开始
数据量很小的时候,我们可以看到,seq scan 比 index scan 更加有效。那是因为 index scan 至少要发生两次I/O,一次是 读取索引块, 一次是读取数据块。当index 很大的时候,情况可能会更加复杂。
postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid; relpages | reltuples | relfilenode | reltype | typname ----------+-----------+-------------+---------+--------- 1 | 100 | 16396 | 16386 | gaotab
数据量为 100条记录。
预估成本:
postgres=# set session enable_seqscan=false; SET postgres=# explain select name from gaotab where id=50; QUERY PLAN --------------------------------------------------------------------- Index Scan using idx_id on gaotab (cost=0.00..8.27 rows=1 width=5) Index Cond: (id = 50) (2 rows)
postgres=# set session enable_seqscan=true; SET postgres=# explain select name from gaotab where id=50; QUERY PLAN ------------------------------------------------------ Seq Scan on gaotab (cost=0.00..2.25 rows=1 width=5