8000 Add a planner support function for starts_with(). · postgrespro/postgres@a148f8b · GitHub
[go: up one dir, main page]
Skip to content

    • Commit a148f8b

    Browse files
    tglsfdctglsfdc
    committed
    Add a planner support function for starts_with().
    This fills in some gaps in planner support for starts_with() and the equivalent ^@ operator: * A condition such as "textcol ^@ constant" can now use a regular btree index, not only an SP-GiST index, so long as the index's collation is C. (This works just like "textcol LIKE 'foo%'".) * "starts_with(textcol, constant)" can be optimized the same as "textcol ^@ constant". * Fixed-prefix LIKE and regex patterns are now more like starts_with() in another way: if you apply one to an SPGiST-indexed column, you'll get an index condition using ^@ rather than two index conditions with >= and <. Per a complaint from Shay Rojansky. Patch by me; thanks to Nathan Bossart for review. Discussion: https://postgr.es/m/232599.1633800229@sss.pgh.pa.us
    1 parent 248c3a9 commit a148f8b

    File tree

    6 files changed

    +95
    -23
    lines changed

    6 files changed

    +95
    -23
    lines changed

    ‎src/backend/utils/adt/like_support.c

    Lines changed: 46 additions & 19 deletions
    Original file line numberDiff line numberDiff line change
    @@ -143,6 +143,14 @@ texticregexeq_support(PG_FUNCTION_ARGS)
    143143
    PG_RETURN_POINTER(like_regex_support(rawreq, Pattern_Type_Regex_IC));
    144144
    }
    145145

    146+
    Datum
    147+
    text_starts_with_support(PG_FUNCTION_ARGS)
    148+
    {
    149+
    Node *rawreq = (Node *) PG_GETARG_POINTER(0);
    150+
    151+
    PG_RETURN_POINTER(like_regex_support(rawreq, Pattern_Type_Prefix));
    152+
    }
    153+
    146154
    /* Common code for the above */
    147155
    static Node *
    148156
    like_regex_support(Node *rawreq, Pattern_Type ptype)
    @@ -246,6 +254,7 @@ match_pattern_prefix(Node *leftop,
    246254
    Oid eqopr;
    247255
    Oid ltopr;
    248256
    Oid geopr;
    257+
    Oid preopr = InvalidOid;
    249258
    bool collation_aware;
    250259
    Expr *expr;
    251260
    FmgrInfo ltproc;
    @@ -302,12 +311,20 @@ match_pattern_prefix(Node *leftop,
    302311
    switch (ldatatype)
    303312
    {
    304313
    case TEXTOID:
    305-
    if (opfamily == TEXT_PATTERN_BTREE_FAM_OID ||
    306-
    opfamily == TEXT_SPGIST_FAM_OID)
    314+
    if (opfamily == TEXT_PATTERN_BTREE_FAM_OID)
    315+
    {
    316+
    eqopr = TextEqualOperator;
    317+
    ltopr = TextPatternLessOperator;
    318+
    geopr = TextPatternGreaterEqualOperator;
    319+
    collation_aware = false;
    320+
    }
    321+
    else if (opfamily == TEXT_SPGIST_FAM_OID)
    307322
    {
    308323
    eqopr = TextEqualOperator;
    309324
    ltopr = TextPatternLessOperator;
    310325
    geopr = TextPatternGreaterEqualOperator;
    326+
    /* This opfamily has direct support for prefixing */
    327+
    preopr = TextPrefixOperator;
    311328
    collation_aware = false;
    312329
    }
    313330
    else
    @@ -360,20 +377,6 @@ match_pattern_prefix(Node *leftop,
    360377
    return NIL;
    361378
    }
    362379

    363-
    /*
    364-
    * If necessary, verify that the index's collation behavior is compatible.
    365-
    * For an exact-match case, we don't have to be picky. Otherwise, insist
    366-
    * that the index collation be "C". Note that here we are looking at the
    367-
    * index's collation, not the expression's collation -- this test is *not*
    368-
    * dependent on the LIKE/regex operator's collation.
    369-
    */
    370-
    if (collation_aware)
    371-
    {
    372-
    if (!(pstatus == Pattern_Prefix_Exact ||
    373-
    lc_collate_is_c(indexcollation)))
    374-
    return NIL;
    375-
    }
    376-
    377380
    /*
    378381
    * If necessary, coerce the prefix constant to the right type. The given
    379382
    * prefix constant is either text or bytea type, therefore the only case
    @@ -409,8 +412,31 @@ match_pattern_prefix(Node *leftop,
    409412
    }
    410413

    411414
    /*
    412-
    * Otherwise, we have a nonempty required prefix of the values.
    413-
    *
    415+
    * Otherwise, we have a nonempty required prefix of the values. Some
    416+
    * opclasses support prefix checks directly, otherwise we'll try to
    417+
    * generate a range constraint.
    418+
    */
    419+
    if (OidIsValid(preopr) && op_in_opfamily(preopr, opfamily))
    420+
    {
    421+
    expr = make_opclause(preopr, BOOLOID, false,
    422+
    (Expr *) leftop, (Expr *) prefix,
    423+
    InvalidOid, indexcollation);
    424+
    result = list_make1(expr);
    425+
    return result;
    426+
    }
    427+
    428+
    /*
    429+
    * Since we need a range constraint, it's only going to work reliably if
    430+
    * the index is collation-insensitive or has "C" collation. Note that
    431+
    * here we are looking at the index's collation, not the expression's
    432+
    * collation -- this test is *not* dependent on the LIKE/regex operator's
    433+
    * collation.
    434+
    */
    435+
    if (collation_aware &&
    436+
    !lc_collate_is_c(indexcollation))
    437+
    return NIL;
    438+
    439+
    /*
    414440
    * We can always say "x >= prefix".
    415441
    */
    416442
    if (!op_in_opfamily(geopr, opfamily))
    @@ -1165,7 +1191,6 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, Oid collation,
    11651191
    case Pattern_Type_Prefix:
    11661192
    /* Prefix type work is trivial. */
    11671193
    result = Pattern_Prefix_Partial;
    1168-
    *rest_selec = 1.0; /* all */
    11691194
    *prefix = makeConst(patt->consttype,
    11701195
    patt->consttypmod,
    11711196
    patt->constcollid,
    @@ -1175,6 +1200,8 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, Oid collation,
    11751200
    patt->constlen),
    11761201
    patt->constisnull,
    11771202
    patt->constbyval);
    1203+
    if (rest_selec != NULL)
    1204+
    *rest_selec = 1.0; /* all */
    11781205
    break;
    11791206
    default:
    11801207
    elog(ERROR, "unrecognized ptype: %d", (int) ptype);

    ‎src/include/catalog/catversion.h

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -53,6 +53,6 @@
    5353
    */
    5454

    5555
    /* yyyymmddN */
    56-
    #define CATALOG_VERSION_NO 202111091
    56+
    #define CATALOG_VERSION_NO 202111171
    5757

    5858
    #endif

    ‎src/include/catalog/pg_operator.dat

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -102,7 +102,7 @@
    102102
    oprright => 'text', oprresult => 'bool', oprcom => '=(text,text)',
    103103
    oprnegate => '<>(text,text)', oprcode => 'texteq', oprrest => 'eqsel',
    104104
    oprjoin => 'eqjoinsel' },
    105-
    { oid => '3877', descr => 'starts with',
    105+
    { oid => '3877', oid_symbol => 'TextPrefixOperator', descr => 'starts with',
    106106
    oprname => '^@', oprleft => 'text', oprright => 'text', oprresult => 'bool',
    107107
    oprcode => 'starts_with', oprrest => 'prefixsel',
    108108
    oprjoin => 'prefixjoinsel' },

    ‎src/include/catalog/pg_proc.dat

    Lines changed: 6 additions & 2 deletions
    Original file line numberDiff line numberDiff line change
    @@ -167,8 +167,12 @@
    167167
    proname => 'texteq', proleakproof => 't', prorettype => 'bool',
    168168
    proargtypes => 'text text', prosrc => 'texteq' },
    169169
    { oid => '3696',
    170-
    proname => 'starts_with', proleakproof => 't', prorettype => 'bool',
    171-
    proargtypes => 'text text', prosrc => 'text_starts_with' },
    170+
    proname => 'starts_with', prosupport => 'text_starts_with_support',
    171+
    proleakproof => 't', prorettype => 'bool', proargtypes => 'text text',
    172+
    prosrc => 'text_starts_with' },
    173+
    { oid => '8923', descr => 'planner support for text_starts_with',
    174+
    proname => 'text_starts_with_support', prorettype => 'internal',
    175+
    proargtypes => 'internal', prosrc => 'text_starts_with_support' },
    172176
    { oid => '68',
    173177
    proname => 'xideq', proleakproof => 't', prorettype => 'bool',
    174178
    proargtypes => 'xid xid', prosrc => 'xideq' },

    ‎src/test/regress/expected/create_index_spgist.out

    Lines changed: 33 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -804,6 +804,22 @@ SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    804804
    2
    805805
    (1 row)
    806806

    807+
    EXPLAIN (COSTS OFF)
    808+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    809+
    QUERY PLAN
    810+
    ------------------------------------------------------------
    811+
    Aggregate
    812+
    -> Index Only Scan using sp_radix_ind on radix_text_tbl
    813+
    Index Cond: (t ^@ 'Worth'::text)
    814+
    Filter: starts_with(t, 'Worth'::text)
    815+
    (4 rows)
    816+
    817+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    818+
    count
    819+
    -------
    820+
    2
    821+
    (1 row)
    822+
    807823
    -- Now check the results from bitmap indexscan
    808824
    SET enable_seqscan = OFF;
    809825
    SET enable_indexscan = OFF;
    @@ -1333,6 +1349,23 @@ SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    13331349
    2
    13341350
    (1 row)
    13351351

    1352+
    EXPLAIN (COSTS OFF)
    1353+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    1354+
    QUERY PLAN
    1355+
    ------------------------------------------------
    1356+
    Aggregate
    1357+
    -> Bitmap Heap Scan on radix_text_tbl
    1358+
    Filter: starts_with(t, 'Worth'::text)
    1359+
    -> Bitmap Index Scan on sp_radix_ind
    1360+
    Index Cond: (t ^@ 'Worth'::text)
    1361+
    (5 rows)
    1362+
    1363+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    1364+
    count
    1365+
    -------
    1366+
    2
    1367+
    (1 row)
    1368+
    13361369
    RESET enable_seqscan;
    13371370
    RESET enable_indexscan;
    13381371
    RESET enable_bitmapscan;

    ‎src/test/regress/sql/create_index_spgist.sql

    Lines changed: 8 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -295,6 +295,10 @@ EXPLAIN (COSTS OFF)
    295295
    SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    296296
    SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    297297

    298+
    EXPLAIN (COSTS OFF)
    299+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    300+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    301+
    298302
    -- Now check the results from bitmap indexscan
    299303
    SET enable_seqscan = OFF;
    300304
    SET enable_indexscan = OFF;
    @@ -424,6 +428,10 @@ EXPLAIN (COSTS OFF)
    424428
    SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    425429
    SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth';
    426430

    431+
    EXPLAIN (COSTS OFF)
    432+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    433+
    SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth');
    434+
    427435
    RESET enable_seqscan;
    428436
    RESET enable_indexscan;
    429437
    RESET enable_bitmapscan;

    0 commit comments

    Comments
     (0)
    0