blocking index creation

From: Neto pr <netopr9(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: blocking index creation
Date: 2017-10-11 12:58:28
Message-ID: CA+wPC0NfZfrhwGCniFWctm_b5z+AyrPAveQtKt74uC44gWw_2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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
<http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png>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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2017-10-11 13:46:19 Re: blocking index creation
Previous Message johannes graën 2017-10-11 12:19:08 Re: performance drop after upgrade (9.6 > 10)