Try the queries here to check locks: https://wiki.postgresql.org/wiki/Lock_Monitoring On Wed, Oct 11, 2017 at 7:35 PM, Neto pr <netopr9@xxxxxxxxx> wrote: > Dear, > With alternative, I tested the creation using concurrency > (CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN); > > from what I saw the index already appeared in the query result, because > before this, the index did not even appear in the result, only the Lineitem > table: > > SELECT > L.mode, c.relname, locktype, l.GRANTED, l.transactionid, > virtualtransaction > FROM pg_locks l, pg_class c > where c.oid = l.relation > > screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg > > Now, I'm waiting to finish creating the index. > > 2017-10-11 19:54 GMT-03:00 Neto pr <netopr9@xxxxxxxxx>: >> >> 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.vondra@xxxxxxxxxxxxxxx>: >>> >>> >>> >>> On 10/11/2017 04:11 PM, Neto pr wrote: >>> > >>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.albe@xxxxxxxxxxx >>> > <mailto:laurenz.albe@xxxxxxxxxxx>>: >>> > >>> > 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 >> >> > -- To understand recursion, one must first understand recursion. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
