Hello all, I ran the query on PG_STAT_ACTIVITY table (Select * From pg_stat_activity), see the complete result in this worksheet of the link below.
https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls The CREATE INDEX command line is identified with the orange background. At this point 18 hours have passed and the creation of a single index has not yet been completed. I have verified that the command is Active status, but I do not know if it's waiting for anything, can you help me analyze the attached output. Regards Neto 2017-10-11 18:08 GMT-03:00 Tomas Vondra <tomas.von...@2ndquadrant.com>: > > > On 10/11/2017 04:11 PM, Neto pr wrote: > > > > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.a...@cybertec.at > > <mailto: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? > > > > Yes, CREATE INDEX acquire SHARE lock, see > > https://www.postgresql.org/docs/9.1/static/explicit-locking.html > > > Before creating the index, should I set the type of transaction lock? > What? > > Eeee? Not sure I understand. The command acquires all necessary locks > automatically. > > > ------------------------------------------------------------ > ------------------------------- > > 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 > > > > > > Well, we see something is holding a SHARE lock on the "lineitem" table, > but we don't really know what the session is doing. > > There's a PID in the pg_locks table, you can use it to lookup the > session in pg_stat_activity which includes the query (and also "state" > column that will tell you if it's active or waiting for a lock. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
