Documentation on text_pattern_ops.

[mijoharas]

Adding a text_pattern_ops index on postgres will optimise the queries used to search for subtrees. I suggest this fact is added to the documentation and the generator.

example index:

      t.index 'ancestry text_pattern_ops', name: 'index_foobars_on_ancestry_text_pattern_ops'

# test is
explain analyse select * from foobars where ancestry LIKE '/123%';
# with text_pattern_ops index.

                        QUERY PLAN               
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foobars  (cost=8.43..45.84 rows=15 width=160) (actual time=0.096..0.097 rows=0 loops=1)
   Filter: ((ancestry)::text ~~ '/123%'::text)
   ->  Bitmap Index Scan on index_foobars_on_ancestry_text_pattern_ops  (cost=0.00..8.43 rows=15 width=0) (actual time=0.093..0.093 rows=0 loops=1)
         Index Cond: (((ancestry)::text ~>=~ '/123'::text) AND ((ancestry)::text ~<~ '/124'::text))
 Planning Time: 0.164 ms
 Execution Time: 0.133 ms
(6 rows)

# without text_pattern_ops index.

QUERY PLAN                                       
------------------------------------------------------------------------------------------------------------
 Seq Scan on foobars  (cost=0.00..78.76 rows=1 width=841) (actual time=2.263..2.265 rows=0 loops=1)
   Filter: ((ancestry)::text ~~ '/123%'::text)
   Rows Removed by Filter: 701
 Planning Time: 0.213 ms
 Execution Time: 2.296 ms
(5 rows)

(this is a tiny test with only a few hundred rows added).

In a related note, in investigating this, I generated some test data with:

parent = nil
1000.times do
  parent = Foobar.create(parent: parent)
end

Which results in # ActiveRecord::StatementInvalid: PG::ProgramLimitExceeded: ERROR: index row size 2712 exceeds btree version 4 maximum 2704 for index "index_foobars_on_ancestry_text_pattern_ops".

(I used a bigint id column. the same thing happens with a normal BTree index)

I think we should document the maximum size of the ancestry column with an index? (maybe this is already documented somewhere maybe I missed it in the docs. This is an important limitation for what I'm trying to do).

[kbrock]

cool, put something together in the docs and we'll get it in.