Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e2Gcj-00088K-Dn for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 13:00:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e2Gci-0006yX-SL for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 13:00:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e2Gan-0003Wv-VA for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 12:58:34 +0000 Received: from mail-qt0-x22e.google.com ([2607:f8b0:400d:c0d::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e2Gal-00063r-0A for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 12:58:33 +0000 Received: by mail-qt0-x22e.google.com with SMTP id f15so4772097qtf.7 for ; Wed, 11 Oct 2017 05:58:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=fhYzFDnEcOtTjowlHaz9gEK7gQGU9t27VJ7YeLuhcZc=; b=SGFnEhE2tw+28jAQKzTzGn3xngGxs3JwJfLl+fLecBGBxic4rpoYxufDGYN1anoB3S fNMrIbSO9oDPx7jwdEsV+SDA4DAKYoYrz6HWAzOy3tXsEuqDIMmTwJYlK3fL3mstwV6C dgEilFSJ6WicT+Si2WeZrcFBzMnMnn6dsQmNNnTUrj+9/+BJ1avCCCBj3wZwUQ4k5cL1 R7YDmqqZZ0fRftG2lNwBx6XNZ9v/zNML0d+bvp/Yz/9qmUcVH9f/6AoPftW/1D4o61mk movtFj9QVDnNXov6jswhm0HAou9TKmQtxvMNBUAKuocBXfSxKN+u28yYXPOiHDJWbZkl bVjQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=fhYzFDnEcOtTjowlHaz9gEK7gQGU9t27VJ7YeLuhcZc=; b=Z303rC6KU6oS/hsVjFStDmqmauLL2q8fDOtaMnvrv0k0b4BBdF0sE0weLAIl7m8izi f/ZezX8uFadk7T9agTQYP5pK3J+Y4B8H3arDyW+lNENuBN96yawlIZJmTM+YD6d1LKiP 8OYKgfO81q2f8H7zqReu2j8yYXtGxDhpBt+6P2yHifRSpiUWmPXRNt574z1pmRPrqV+5 o1JPEf4herApDBdivd24xz7ZAhTMGbHuPmaOewmu/9Od8ZRQZjVFsbP6A7COzNGfQEHr X+hk/uI35kU2JuKPyVLLghXRAAmC3R5h2pbzQ31qyHBUztU6daRxaHMcdkb613jglQoL zDQw== X-Gm-Message-State: AMCzsaVqdWYb0EvMFNU3HiKRorp/iwrEHoileiToRJozLwcl4SLZjaNu Px+mqGbkvDw7bIm2WlOarVGjlBahrEogEHuFsP+YFw== X-Google-Smtp-Source: AOwi7QCBBSoOCE+Q+ibzLUfqavZ4LDnZ38MnzMLwRtTz4f96LLOTrr/Z+Ynmf+wxBChUeCi7TpLRV5rnXk6GaM6zTx4= X-Received: by 10.37.184.2 with SMTP id v2mr4104828ybj.506.1507726708823; Wed, 11 Oct 2017 05:58:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.37.107.69 with HTTP; Wed, 11 Oct 2017 05:58:28 -0700 (PDT) From: Neto pr Date: Wed, 11 Oct 2017 09:58:28 -0300 Message-ID: Subject: blocking index creation To: postgres performance list Content-Type: multipart/alternative; boundary="089e0822c9d4e5d949055b44faf8" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --089e0822c9d4e5d949055b44faf8 Content-Type: text/plain; charset="UTF-8" Hello all, My scenario is: postgresql 10, Processor Xeon 2.8GHz / 4-core- 8gb Ram, OS Debian 8. When creating index on table of approximately 10GB of data, the DBMS hangs (I think), because even after waiting 10 hours there was no return of the command. It happened by creating Hash indexes and B + tree indexes. However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY). The data environment is the LINEITEM table (TPC-H benchmark) of link 1 below. The columns/indexes that caught the creation were: * Hash Index in column: L_TAX * Btree Index in column: L_RECEIPTDATE. If someone has a hint how to speed up index creation so that it completes successfully. I know that PostgreSQL 10 has some parallelism features and since my server is dedicated only to the DBMS, do I change the parameters: force_parallel_mode, max_parallel_workers_per_gather could speed up index creation on large tables? Any tip is welcome. DDL comand : L_ORDERKEY BIGINT NOT NULL, - references O_ORDERKEY L_PARTKEY BIGINT NOT NULL, - references P_PARTKEY (compound fk to PARTSUPP) L_SUPPKEY BIGINT NOT NULL, - references S_SUPPKEY (compound fk to PARTSUPP) L_LINENUMBER INTEGER, L_QUANTITY DECIMAL, L_EXTENDEDPRICE DECIMAL, L_DISCOUNT DECIMAL, L_TAX DECIMAL, L_RETURNFLAG CHAR (1), L_LINESTATUS CHAR (1), L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT CHAR (25), L_SHIPMODE CHAR (10), L_COMMENT VARCHAR (44),PRIMARY KEY (L_ORDERKEY, L_LINENUMBER) 1- http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png best Regards Neto --089e0822c9d4e5d949055b44faf8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hello all,

My scenario is: postgresql 10, Processor Xeon 2.8GH= z / 4-core- 8gb Ram, OS Debian 8.

When creating index on table of approximately 10GB of data, the DBMS=20 hangs (I think), because even after waiting 10 hours there was no return of the command.=20 It happened by creating Hash indexes and B + tree indexes. However, for=20 some columns, it was successfully (L_RETURNFLAG, L_PARTKEY). The data environment is the LINEITEM table (TPC-H benchmark) of link 1below. The columns/indexes that caught the creati= on were: * Hash Index in column: L_TAX * Btree Index in column: L_RECEIPTDATE.

If someone has a hint how to speed up index creation so that it=20 completes successfully. I know that PostgreSQL 10 has some parallelism=20 features and since my server is dedicated only to the DBMS, do I change=20 the parameters: force_parallel_mode, max_parallel_workers_per_gather=20 could speed up index creation on large tables? Any tip is welcome.

<=
span class=3D"gmail-pln">DDL comand :
L_ORDERKEY BIGINT NOT NULL, - refere=
nces O_ORDERKEY
L_PARTKEY BIGINT NOT NULL, =
- referen=
ces P_PARTKEY (compound fk to PARTSUPP)
L_SUPPKEY BIGINT NOT NULL, =
- referen=
ces S_SUPPKEY (compound fk to PARTSUPP)
L_LINENUMBER INTEGER,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT VARCHAR (44),
PRIMARY <=
/span>KEY (L_ORDERKEY, L_LINENUMBE=
R)

1- http://kejser.org/wp-content/uploads/2014/= 06/image_thumb2.png

best Regards

Neto

--089e0822c9d4e5d949055b44faf8--