Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Unanswered: Create more than one index in single call

    We routinely create 7500+ indexes.

    In order to speed up index creation, it is desirable to create more than one index using a single call.

    Here is a sample for the extended syntax:
    create index idxName on tabA(col1), tabA(col2), tabB(col3)

    drop table already supports multiple tables.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Even if the syntax would be supported, that wouldn't speed up anything.

    You only spare some (network) roundtrips to the database, but as the major work is the physical creation of the index, that wouldn't make much difference.

  3. #3
    Join Date
    Sep 2010
    Posts
    5
    Is there some way to parallelize index creation in Postgres ?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by tedyu View Post
    Is there some way to parallelize index creation in Postgres ?
    Simply kick off the CREATE INDEX from different connections.

  5. #5
    Join Date
    Sep 2010
    Posts
    5
    Can someone outline how drop table works when multiple table names are specified ? Reference to source code would be great.

    Assuming dropping multiple tables in one call has superior performance to calling drop table with single table name multiple times, I am wondering if index creation can follow similar approach.

    The assumption here is that the indexes involved are independent and the order of the index names is not significant.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by tedyu View Post
    Assuming dropping multiple tables in one call has superior performance to calling drop table with single table name multiple times
    It isn't faster.

    I am wondering if index creation can follow similar approach.
    See my first post.
    I can't be faster, the work that needs to be done is always the same.

  7. #7
    Join Date
    Sep 2010
    Posts
    5

    loaradru Bonhoeffer

    Can I assume dropping table with multiple table names is slower than multiple calls with single table name ?
    Some comparison numbers would help.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by tedyu View Post
    Can I assume dropping table with multiple table names is slower than multiple calls with single table name
    I don't think there will be any difference in performance. Because - again - the work that needs to be done by the server is identical. That is true for dropping table and for creating indexes.

    Some comparison numbers would help.
    Why don't you just try it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •