postgresql 索引之 hash的使用详解

postgres=# \h create index

Command:   CREATE INDEX

Description: define a new index

Syntax:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]

   ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

   [ WITH ( storage_parameter = value [, ... ] ) ]

   [ TABLESPACE tablespace_name ]

   [ WHERE predicate ]

postgres=# drop table tmp_t0;

DROP TABLE

postgres=# create table tmp_t0(c0 varchar (100),c1 varchar (100));

CREATE TABLE

postgres=# insert into tmp_t0(c0,c1) select md5(id:: varchar ),md5((id+id):: varchar ) from generate_series(1,100000) as id;

INSERT 0 100000

postgres=# create index idx_tmp_t0_1 on tmp_t0 using hash(c0);

CREATE INDEX

postgres=# \d+ tmp_t0

                      Table "public.tmp_t0"

  Column |     Type     | Collation | Nullable | Default | Storage | Stats target | Description

--------+------------------------+-----------+----------+---------+----------+--------------+-------------

  c0   | character varying (100) |      |     |     | extended |       |

  c1   | character varying (100) |      |     |     | extended |       |

Indexes:

   "idx_tmp_t0_1" hash (c0)

postgres=# explain select * from tmp_t0 where c0 = 'd3d9446802a44259755d38e6d163e820' ;

                  QUERY PLAN                

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

  Index Scan using idx_tmp_t0_1 on tmp_t0 (cost=0.00..8.02 rows =1 width=66)

   Index Cond: ((c0)::text = 'd3d9446802a44259755d38e6d163e820' ::text)

(2 rows )

demo=# create index on flights using hash(flight_no);

demo=# explain (costs off ) select * from flights where flight_no = 'PG0001' ;

            QUERY PLAN          

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

  Bitmap Heap Scan on flights

   Recheck Cond: (flight_no = 'PG0001' ::bpchar)

   -> Bitmap Index Scan on flights_flight_no_idx

      Index Cond: (flight_no = 'PG0001' ::bpchar)

(4 rows )

demo=# select   opf.opfname as opfamily_name,

      amproc.amproc::regproc AS opfamily_procedure

from    pg_am am,

      pg_opfamily opf,

      pg_amproc amproc

where   opf.opfmethod = am.oid

and    amproc.amprocfamily = opf.oid

and    am.amname = 'hash'

order by opfamily_name,

      opfamily_procedure;

    opfamily_name  |  opfamily_procedure 

--------------------+-------------------------

  abstime_ops    | hashint4extended

  abstime_ops    | hashint4

  aclitem_ops    | hash_aclitem

  aclitem_ops    | hash_aclitem_extended

  array_ops     | hash_array

  array_ops     | hash_array_extended

  bool_ops      | hashcharextended

  bool_ops      | hashchar

  bpchar_ops     | hashbpcharextended

  bpchar_ops     | hashbpchar

  bpchar_pattern_ops | hashbpcharextended

  bpchar_pattern_ops | hashbpchar

  bytea_ops     | hashvarlena

  bytea_ops     | hashvarlenaextended

  char_ops      | hashcharextended

  char_ops      | hashchar

  cid_ops      | hashint4extended

  cid_ops      | hashint4

  date_ops      | hashint4extended

  date_ops      | hashint4

  enum_ops      | hashenumextended

  enum_ops      | hashenum

  float_ops     | hashfloat4extended

  float_ops     | hashfloat8extended

  float_ops     | hashfloat4

  float_ops     | hashfloat8

  ...

hank=# select hashtext( 'zhang' );

  hashtext 

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

  -1172392837

(1 row)

hank=# select hashint4(10);

  hashint4 

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

  -1547814713

(1 row)

hank=# select a.amname, p. name , pg_indexam_has_property(a.oid,p. name )

hank-# from pg_am a,

hank-#   unnest(array[ 'can_order' , 'can_unique' , 'can_multi_col' , 'can_exclude' ]) p( name )

hank-# where a.amname = 'hash'

hank-# order by a.amname;

  amname |   name    | pg_indexam_has_property

--------+---------------+-------------------------

  hash  | can_order   | f

  hash  | can_unique  | f

  hash  | can_multi_col | f

  hash  | can_exclude  | t

(4 rows )

hank=# select p. name , pg_index_has_property( 'hank.idx_test_name' ::regclass,p. name )

hank-# from unnest(array[

hank(#    'clusterable' , 'index_scan' , 'bitmap_scan' , 'backward_scan'

hank(#   ]) p( name );

    name    | pg_index_has_property

---------------+-----------------------

  clusterable  | f

  index_scan  | t

  bitmap_scan  | t

  backward_scan | t

(4 rows )

hank=# select p. name ,

hank-#   pg_index_column_has_property( 'hank.idx_test_name' ::regclass,1,p. name )

hank-# from unnest(array[

hank(#    'asc' , 'desc' , 'nulls_first' , 'nulls_last' , 'orderable' , 'distance_orderable' ,

hank(#    'returnable' , 'search_array' , 'search_nulls'

hank(#   ]) p( name );

     name     | pg_index_column_has_property

--------------------+------------------------------

  asc         | f

  desc         | f

  nulls_first    | f

  nulls_last     | f

  orderable     | f

  distance_orderable | f

  returnable     | f

  search_array    | f

  search_nulls    | f

(9 rows )

hank=# select   opf.opfname AS opfamily_name,

hank-#     amop.amopopr::regoperator AS opfamily_operator

hank-# from    pg_am am,

hank-#     pg_opfamily opf,

hank-#     pg_amop amop

hank-# where   opf.opfmethod = am.oid

hank-# and    amop.amopfamily = opf.oid

hank-# and    am.amname = 'hash'

hank-# order by opfamily_name,

hank-#     opfamily_operator;

   opfamily_name  |           opfamily_operator          

--------------------+------------------------------------------------------------

  abstime_ops    | =(abstime,abstime)

  aclitem_ops    | =(aclitem,aclitem)

  array_ops     | =(anyarray,anyarray)

  bool_ops      | =(boolean,boolean)

  bpchar_ops     | =( character , character )

  bpchar_pattern_ops | =( character , character )

  bytea_ops     | =(bytea,bytea)

  char_ops      | =( "char" , "char" )

  cid_ops      | =(cid,cid)

  date_ops      | =( date , date )

  enum_ops      | =(anyenum,anyenum)

  float_ops     | =( real , real )

  float_ops     | =( double precision , double precision )

  float_ops     | =( real , double precision )

  float_ops     | =( double precision , real )

  hash_hstore_ops  | =(hstore,hstore)

  integer_ops    | =( integer , bigint )

  integer_ops    | =( smallint , smallint )

  integer_ops    | =( integer , integer )

  integer_ops    | =( bigint , bigint )

  integer_ops    | =( bigint , integer )

  integer_ops    | =( smallint , integer )

  integer_ops    | =( integer , smallint )

  integer_ops    | =( smallint , bigint )

  integer_ops    | =( bigint , smallint )

  interval_ops    | =(interval,interval)

  jsonb_ops     | =(jsonb,jsonb)

  macaddr8_ops    | =(macaddr8,macaddr8)

  macaddr_ops    | =(macaddr,macaddr)

  name_ops      | =( name , name )

  network_ops    | =(inet,inet)

  numeric_ops    | =( numeric , numeric )

  oid_ops      | =(oid,oid)

  oidvector_ops   | =(oidvector,oidvector)

  pg_lsn_ops     | =(pg_lsn,pg_lsn)

  range_ops     | =(anyrange,anyrange)

  reltime_ops    | =(reltime,reltime)

  text_ops      | =(text,text)

  text_pattern_ops  | =(text,text)

  time_ops      | =( time without time zone, time without time zone)

  timestamp_ops   | =( timestamp without time zone, timestamp without time zone)

  timestamptz_ops  | =( timestamp with time zone, timestamp with time zone)

  timetz_ops     | =( time with time zone, time with time zone)

  uuid_ops      | =(uuid,uuid)

  xid_ops      | =(xid,xid)

create extension pageinspect;

hank=# select hash_page_type(get_raw_page( 'hank.idx_test_name' ,0));

  hash_page_type

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

  metapage

(1 row)

hank=# select ntuples, maxbucket

hank-# from hash_metapage_info(get_raw_page( 'hank.idx_test_name' ,0)); 

  ntuples | maxbucket

---------+-----------

   1000 |     3

(1 row)

hank=# select hash_page_type(get_raw_page( 'hank.idx_test_name' ,1));

  hash_page_type

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

  bucket

(1 row)

hank=# select live_items, dead_items

hank-# from hash_page_stats(get_raw_page( 'hank.idx_test_name' ,1)); 

  live_items | dead_items

------------+------------

     407 |     0

(1 row)