What are the ~>=~ and ~<~ operators in PostgreSQL?



  • My query's filter was school ~ '^nebraska' and this was the resulting query plan. What is ~>=~ and ~? These appear to be ungoogleable and I can't find them in the PostgreSQL docs.

    ->  Nested Loop
            ->  Index Scan using schools_school_712ef684_like on schools u2
                Index Cond: (((school)::text ~>=~ 'nebraska'::text) AND ((school)::text ~

    The schools_school_712ef684_like index is a btree (school varchar_pattern_ops) created by Django automatically.


  • QA Engineer

    select opcname, amopstrategy,amopopr::regoperator
    from pg_am am
    join pg_opfamily opf on am.oid =opf.opfmethod
    join pg_opclass opc on opf.oid = opc.opcfamily
    join pg_amop amop  on opc.opcfamily = amop.amopfamily
    where amname = 'btree'
    and opcname in ('text_ops', 'text_pattern_ops')
    and amoplefttype = 'text'::regtype
    and amoprighttype = 'text'::regtype
    order by 1,2;
    

    return

    /*
        opcname      | amopstrategy |     amopopr
    ------------------+--------------+-----------------
     text_ops         |            1 | =(text,text)
     text_ops         |            5 | >(text,text)
     text_pattern_ops |            1 | ~=~(text,text)
     text_pattern_ops |            5 | ~>~(text,text)
     */
    

    Then link:

    • https://www.postgresql.org/docs/current/indexes-opclass.html
    • https://www.postgresql.org/docs/current/catalog-pg-opclass.html
    • https://www.postgresql.org/docs/current/catalog-pg-amop.html

    I hope my understand is correct.


    Faster text pattern match need index. Index is access methods. Then we need index access operator classes.-> pg_opclass For each opclass_name there is detail info -> pg_amop


    exmaple:
    explain (costs off) select * from tickets where passenger_name like 'ELENA%';
    query plan:

                                                 QUERY PLAN
    --------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on tickets
       Filter: (passenger_name ~~ 'ELENA%'::text)
       ->  Bitmap Index Scan on tickets_passenger_name_pattern_idx
             Index Cond: ((passenger_name ~>=~ 'ELENA'::text) AND (passenger_name ~

    predicate1 (passenger_name ~>=~ 'ELENA'::text
    predicate2 (passenger_name ~

    predicate1 mean that pattern_string larger equal 'ELENA' means either ELENA or ELENA.... or ELENB... [B can any character larger than A]. three dot mean characters.
    predicate2 mean pattern_string smaller than ELENB.
    match predicate1 and predicate2 is either ELENA or ELENA...


    update

     select amopopr::regoperator, amopstrategy as st, opr.oprcode, obj_description(opr.oid,'pg_operator') as description
         from pg_am am join pg_opclass opc on am.oid = opc.opcmethod
                    join pg_amop amop on amopfamily = opc.opcfamily
                    join pg_operator opr  on amop.amopopr = opr.oid
    where amname = 'btree' and opcname = 'text_pattern_ops'order by 2;
    

    return:

         amopopr     | st |     oprcode     |      description
    -----------------+----+-----------------+-----------------------
     ~=~(text,text) |  4 | text_pattern_ge | greater than or equal
     ~>~(text,text)  |  5 | text_pattern_gt | greater than
    

    I think the key part is understand tilde operator.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2