2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.a...@cybertec.at>: > Neto pr wrote: > > 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). > > > If someone has a hint how to speed up index creation so that it > completes successfully. > > Look if CREATE INDEX is running or waiting for a lock (check the > "pg_locks" table, see if the backend consumes CPU time). > > In this moment now, there is an index being created in the Lineitem table (+ - 10 Gb), and apparently it is locked, since it started 7 hours ago. I've looked at the pg_locks table and look at the result, it's with "ShareLock" lock mode. Is this blocking correct? or should it be another type?
Before creating the index, should I set the type of transaction lock? What? ------------------------------------------------------------------------------------------- SELECT L.mode, c.relname, locktype, l.GRANTED, l.transactionid, virtualtransaction FROM pg_locks l, pg_class c where c.oid = l.relation -------------- RESULT -------------------------------------------------------------- AccessShareLock pg_class_tblspc_relfilenode_index relation TRUE (null) 3/71 AccessShareLock pg_class_relname_nsp_index relation TRUE (null) 3/71 AccessShareLock pg_class_oid_index relation TRUE (null) 3/71 AccessShareLock pg_class relation TRUE (null) 3/71 AccessShareLock pg_locks relation TRUE (null) 3/71 ShareLock lineitem relation TRUE (null) 21/3769 > Maybe there is a long-running transaction that blocks the > ACCESS EXCLUSIVE lock required. It could also be a prepared > transaction. > > Yours, > Laurenz Albe > Best Regards Neto