DBA Page

postgresql index types:
                                    1.B-tree - Small set of data.  
                                    2.Hash - not recommeded due to entries in wal log , recovery not possible(=)
                                    3.GiST -   Generalized Search Tree, used for complex data types like geometric data.( geometric data types and full-text searches.)
                                    4.SPGIST - Space-partitioned Generalized Search Tree(GIS, multimedia, phone routing, and IP routing.)
                                    5.GIN - Generalized Inverted Index, used for full-text search and array data types.(hstore, array, jsonb, and range types)
                                    6.BRIN - Block Range INdex, used for large sets of data with timestamp and date ranges.
                                    

                      explain analyze select * from t1 where id=5;
                      create index in_t1_id on t1 using btree(id);
                      

                      GIN stands for Generalized Inverted Index.
                          The most common data types that fall into this bucket are:
                          hStore
                          Arrays
                          Range types
                          JSONB
                          tsvector

                          operators:
                                  <@
                                  @>
                                  =
                                  && 


                        create table ts(doc text, doc_tsv tsvector);

                            insert into ts(doc) values
                            ('Can a sheet slitter slit sheets?'),
                            ('How many sheets could a sheet slitter slit?'),
                            ('I slit a sheet, a sheet I slit.'),
                            ('Upon a slitted sheet I sit.'),
                            ('Whoever slit the sheets is a good sheet slitter.'),
                            ('I am a sheet slitter.'),
                            ('I slit sheets.'),
                            ('I am the sleekest sheet slitter that ever slit sheets.'),
                            ('She slits the sheet she sits on.'); 

                            update ts set doc_tsv = to_tsvector(doc);

                            create index on ts using gin(doc_tsv);


                            explain analyze select doc from ts where doc_tsv @@ to_tsquery('many & slitter');
                      

                           GiST indexes allow a building of general tree structures.
                          GiST indexes are useful in indexing geometric data types and full-text search.

                          operators:
                                <<
                                &<
                                &>
                                >>
                                <<|
                                &<|
                                |&>
                                |>>
                                @>
                                <@
                                ~=
                                &&


                          example::
                          create table points(p point);
                          insert into points(p) values
                          (point '(1,1)'), (point '(3,2)'), (point '(6,3)'),
                          (point '(5,5)'), (point '(7,8)'), (point '(8,6)');

                          explain analyze select * from points where p <@ box '(2,1),(7,4)';

                          create index on points using gist(p);

                          create index ip1 on points using gist(p);

                          explain analyze select * from points where p <@ box '(2,1),(7,4)';

                      

 sp-gist:
SP-GiST is an abbreviation for space-partitioned GiST. SP-GiST supports partitioned search trees,
which facilitate development of a wide range of different non-balanced data structures, such as quad-trees, k-d trees, and radix trees (tries).
which support indexed queries using these operators:

operators:
<<
>>
~=
<@
<^

create table points1(p point);

insert into points1(p) values
(point '(1,1)'), (point '(3,2)'), (point '(6,3)'),
(point '(5,5)'), (point '(7,8)'), (point '(8,6)');


select amop.amopopr::regoperator, amop.amopstrategy
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'quad_point_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'spgist'


create index points_quad_idx on points1 using spgist(p);

explain (costs off) select * from points1 where p >^ point '(2,7)';