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 1below. 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