public inbox for [email protected]
help / color / mirror / Atom feedFrom: Neto pr <[email protected]>
To: Laurenz Albe <[email protected]>
To: postgres performance list <[email protected]>
Subject: Re: blocking index creation
Date: Wed, 11 Oct 2017 11:11:23 -0300
Message-ID: <CA+wPC0PC7PARUOF6GB3eQTU8x0fY1Cy4H1R0Bvm-x4fZb0gVXQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+wPC0NfZfrhwGCniFWctm_b5z+AyrPAveQtKt74uC44gWw_2Q@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
2017-10-11 10:46 GMT-03:00 Laurenz Albe <[email protected]>:
> 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: blocking index creation
In-Reply-To: <CA+wPC0PC7PARUOF6GB3eQTU8x0fY1Cy4H1R0Bvm-x4fZb0gVXQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox