Re: Bundle of 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(at)svana(dot)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(at)sigaev(dot)ru
> WWW: http://www.sigaev.ru/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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