Re: Bundle of patches - Mailing list pgsql-patches

I assume we have taken all the patches from here we want.

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

Teodor Sigaev wrote:
> The 1C (http://www.1c.ru/) company kindly permits to publish a set of patches
> we (Oleg and me) developed during our work on porting of the '1C:Enterprise'
> system to support the PostgreSQL database.
>
> We would like to suggest to commit they to HEAD.
>
>
> 1) Typmod for user-defined types
>    http://www.sigaev.ru/misc/user_defined_typmod-0.7.gz
>    Patch is based on ideas from
>    http://archives.postgresql.org/pgsql-hackers/2004-06/msg00932.php
>    http://archives.postgresql.org/pgsql-hackers/2005-08/msg01007.php
>
>    Patch adds to type definition two optional function: typmodinput and
>    typmodoutput. That allows to develop user-defined types with type's
>    modificators. Built-in types use typmod input/output functions too.
>    Typmod internally is represented  as non-negative int4 value,
>    but patch allows to point list of integer in type definition. So,
>    NUMERIC type works with a help of typmodin/typmodout function.
>
>
> 2) ORDER BY .. [ NULLS ( FIRST | LAST ) ]
>    http://www.sigaev.ru/misc/NULLS_82-0.5.gz
>    Allow to sort NULLs as greater or lesser than any value. The goal was to
>    simplificate migrations from MySQL/MS SQL which think that NULL is less.
>    Also, syntax conforms to SQL2003. It operate on gram.y level, and
>    adds 'field is [not] null' qualification to sortClause.
>    Note, to allow queries like 'select .. union .. order by f nulls first'
>    pgsql now can rewrite that query to
>    'select * from (select .. union ..) order by f nulls first'. This solves the
>    problem with 'resjunk' column in SelectStmt->sortClause.
>
> 3) Allow to use index for IS [NOT] NULL
>    http://www.sigaev.ru/misc/indexnulls_82-0.6.gz
>    Initially patch was developed by Martijn van Oosterhout <kleptog@svana.org>.
>    But it's reworked  and support of searching NULLS to GiST too. Patch
>    adds new column named amsearchnull to pg_am. To recognize IS NULL clause
>    ScanKey->sk_flags contains (SK_ISNULL & SK_INDEXFINDNULL) and
>    ScanKey->sk_strategy == BTEqualStrategyNumber. For IS NOT NULL,
>    ScanKey->sk_strategy == BTLessStrategyNumber. Thats because NULLs are
>    treated greater than any value. It might be look some odd that
>    for IS [NOT] NULL clauses we use Btree strategy numbers even for GiST,
>    but if sk_flags contains SK_ISNULL then we never call user-defined functions.
>
> 4) OR clauses optimizations
>    http://www.sigaev.ru/misc/OR_82-0.6.gz
>    Patch can suggest new indexpaths to optimizer for ORed clauses. Patch uses
>    generate_bitmapscan and predicate_implied_by/predicate_refuted_by machineries
>
> 4.1) Allow any useful common restriction clauses to be extracted from
>    OR-of-AND quals. Also, it allows to combine several different
>    operations to one which can be used in index scan.
>    SELECT
>          a, b
>    FROM
>          tst
>    WHERE ( a = 50000 ) OR ( a > 50000 AND b > 50000 )
>    ORDER BY a, b
>    LIMIT 20;
>    Limit  (cost=0.00..2.95 rows=20 width=8) (actual time=0.271..0.677 rows=20
> loops=1)
>     ->  Index Scan using abidx on tst  (cost=0.00..3671.26 rows=24878 width=8)
> (actual time=0.265..0.611 rows=20 loops=1)
>           Index Cond: (a >= 50000)
>           Filter: ((a = 50000) OR ((a > 50000) AND (b > 50000)))
> 4.2) When OR clauses aren't intersect and use the same index, it's possible
>    to just concatenate results of indexscans. For that, now postgres may use
>    Append node. Append node is modified to have a pathkeys.
>
>    SELECT
>          a
>    FROM
>          tst
>    WHERE ( a > 60000 AND a < 61000 ) OR ( a > 20000 AND a < 21000 )
>    ORDER BY ASC
>    LIMIT 20;
>    Limit  (cost=0.00..39.86 rows=20 width=4) (actual time=0.364..0.883 rows=20
> loops=1)
>     ->  Result  (cost=0.00..4001.55 rows=2008 width=4) (actual time=0.359..0.824
> rows=20 loops=1)
>           ->  Append  (cost=0.00..4001.55 rows=2008 width=4) (actual
> time=0.349..0.742 rows=20 loops=1)
>                 ->  Index Scan using aidx on tst  (cost=0.00..2000.42 rows=990
> width=4) (actual time=0.346..0.684 rows=20 loops=1)
>                       Index Cond: ((a > 20000) AND (a < 21000))
>                 ->  Index Scan using aidx on tst  (cost=0.00..2001.12 rows=1018
> width=4) (never executed)
>                       Index Cond: ((a > 60000) AND (a < 61000))
>
>    Also, if there is a 'ORDER BY' clause, childs nodes may be ordered by theys
>    ranges (compare plan with previous one).
>    SELECT
>          a
>    FROM
>          tst
>    WHERE ( a > 60000 AND a < 61000 ) OR ( a > 20000 AND a < 21000 )
>    ORDER BY a DESC
>    LIMIT 20;
>    Limit  (cost=0.00..39.86 rows=20 width=4) (actual time=0.162..0.651 rows=20
> loops=1)
>     ->  Result  (cost=0.00..4001.55 rows=2008 width=4) (actual time=0.157..0.589
> rows=20 loops=1)
>           ->  Append  (cost=0.00..4001.55 rows=2008 width=4) (actual
> time=0.149..0.511 rows=20 loops=1)
>                 ->  Index Scan Backward using aidx on tst  (cost=0.00..2001.12
> rows=1018 width=4) (actual time=0.145..0.450 rows=20 loops=1)
>                       Index Cond: ((a > 60000) AND (a < 61000))
>                 ->  Index Scan Backward using aidx on tst  (cost=0.00..2000.42
> rows=990 width=4) (never executed)
>                       Index Cond: ((a > 20000) AND (a < 21000))
>
> 4.3) As side effect of previous point, overlapped clauses can be eliminated:
>
>    SELECT
>          a
>    FROM
>          tst
>    WHERE ( a > 50000 AND a < 61000 ) OR ( a > 60000 AND a < 60100 )
>    ORDER BY a
>    LIMIT 20;
>    Limit  (cost=0.00..4.14 rows=20 width=4) (actual time=0.168..1.001 rows=20
> loops=1)
>     ->  Index Scan using aidx on tst  (cost=0.00..2344.85 rows=11338 width=4)
> (actual time=0.162..0.935 rows=20 loops=1)
>           Index Cond: ((a > 50000) AND (a < 61000))
>
>
>
> --
> Teodor Sigaev                                   E-mail: teodor@sigaev.ru
>                                                     WWW: http://www.sigaev.ru/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +