Re: Table Partitioning in Postgres: - Mailing list pgsql-general

> -----Original Message-----
> From: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
> Sent: Tuesday, February 18, 2003 10:54 AM
> To: Shridhar Daithankar<shridhar_daithankar@persistent.co.in>
> Cc: PGSQL General (E-mail)
> Subject: Re: [GENERAL] Table Partitioning in Postgres:
>
>
> Shridhar,
>
> I must disagree with "that's is an OS's job".
> OSs try to be generic.  With databases, we know more about
> the data structure.
>
> If a large table could be split (partitioned) based on
> specific key, we could expect huge improvements for agregates
> queries for example when that key is involved.
>
> Also, DBA must be able to place table on different file
> systems. They know more about the application than the OS does.
>
> Even Postgresql has to be told to perform vaccum and analyze.
> If the OS had enough intelligence we could trust it to do a
> good job, but until then ...

Oracle's Rdb has a nice syntax for index creation that takes into
account separate areas.  This will probably look horrible, but here is
the ASCII representation of the syntax grammar:

CREATE INDEX Subtopic? form

CREATE

  INDEX

    Format


       CREATE - ----------- -> INDEX <index-name> ------------------
                -> UNIQUE -
        ------------------------------------------------------------
        -- ----------------------------------- -> ON <table-name> --
           -> STORED NAME IS <stored-name> ---
        ----------------------------- <-----------------------------
        > (  > <column-name> --
                 --------------
                 - -------------  ----------------------------- - - ) -
                   > ASCENDING -  > SIZE IS <n> ---------------
                   > DESCENDING   > MAPPING VALUES <l> TO <h> -
             ---------------------- , <--------------------------
        ------------------------------ <-------------------------------
         ---------------- - ----------------------- -
         -> type-clause -   -> compression-clause -
        ---------------------------------------------
         ----------------------- -------------------------------------->
         -> index-store-clause -



       type-clause =

       -> TYPE IS  > HASHED  -------------
------------------------------ >
                             > ORDERED ---

                             > SCATTERED -

                   > SORTED  ----------------------------------------- -

                             > RANKED - -----------------------------

                                        > DUPLICATES ARE COMPRESSED -

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

                           - -----------------------
--------------------
                             > sorted-index-clause -



       sorted-index-clause =

       -- - -> NODE SIZE <number-bytes> ---------- - -->
            -> PERCENT FILL <percentage> ---------
            -> USAGE - -> UPDATE - ---------------
                       -> QUERY --
          ------------------- <---------------------



       compression-clause =

       - -> ENABLE COMPRESSION - ------------------------------ - ->
                                 > ( MINIMUM RUN LENGTH <n> ) -
         -> DISABLE COMPRESSION ---------------------------------



       index-store-clause =

       STORE ----------------------
        ---------------------------
         -> IN <area-name> - ---------------------------------- ------ >
                             -> ( -> threshold-clause -> ) ----
         -> USING -> ( - --> <column-name> - -> ) ----------
                         ------- , <--------
         ---------------------------------------------------
          > IN <area-name> - ------------------------------- -
                             -> ( -> threshold-clause -> ) -
            --------------------------------------------------
            -> WITH LIMIT OF -> ( - --> <literal> - -> ) --- ---
                                    ------ , <-----
          -----------------------<--------------------------
        --------------------------------------------------------
         ------------------------------------------------------------
         -> OTHERWISE IN <area-name>  ------------------------------
                                      > ( -> threshold-clause -> ) -



       threshold-clause =

       -- -> THRESHOLD - -> IS - -> ( --> <val1> --> ) --------- ->
                         -> OF -

          -> THRESHOLDS - -> ARE - -----------
                          -> OF  -
            ----------------------------------
            -> ( --> <val1> - --------------------------- -> ) -
                              -> , <val2> - -------------
                                            -> , <val3> -



CREATE INDEX Subtopic?
[snip]