Re: Create index concurrently hanging with big table on pgsql 9.3.12

You still haven't stated why you think it is blocked?

Ouput from iotop:

3990 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.00 % postgres:
postgres flip [local] CREATE INDEX

The process isn't reading or writing anything for many hours, but it's
using almost 90% of CPU.

How long has it been taking?

backend_start | 2016-05-07 11:48:39.218398-03

More than 50 hours.

What is your maintenance_work_mem set to?

maintenance_work_mem = 352MB

2016-05-09 14:34 GMT-03:00 Joshua D. Drake <jd(at)commandprompt(dot)com>:

> On 05/09/2016 10:32 AM, Robert Anderson wrote:
>
>> Only one line returned:
>>
>> postgres=# select * from pg_stat_activity where pid=3990;
>> -[ RECORD 1 ]----+----------------------------------------
>> datid | 16434
>> datname | flip
>> pid | 3990
>> usesysid | 10
>> usename | postgres
>> application_name | psql
>> client_addr |
>> client_hostname |
>> client_port | -1
>> backend_start | 2016-05-07 11:48:39.218398-03
>> xact_start | 2016-05-07 11:48:43.417734-03
>> query_start | 2016-05-07 11:48:43.417734-03
>> state_change | 2016-05-07 11:48:43.417742-03
>> waiting | f
>> state | active
>> query | CREATE INDEX CONCURRENTLY index_texto
>> | ON flip_pagina_edicao
>> | USING hash
>> | (texto COLLATE pg_catalog."default");
>>
>
> So it isn't blocked by a lock. You still haven't stated why you think it
> is blocked? How long has it been taking? What is your maintenance_work_mem
> set to?
>
>
> JD
>
>
>
> --
> Command Prompt, Inc. http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>