Parallel index creation & pg_stat_activity

 

Hi,

I created an index on a 11devel base while sampling pg_stat_activity with a little tool. Tool catches a line if state = active. Collected rows are aggregated and sorted by activity percentage.

 

Test environment :

 

select version();

                                                                      version

----------------------------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 11devel (Debian 11~~devel~20180227.2330-1~420.git51057fe.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-5) 7.3.0, 64-bit

(1 ligne)

 

Temps : 0,762 ms

 

create table t1(c1 bigint, c2 double precision, c3 text);

CREATE TABLE

 

insert into t1 select generate_series(1,100000000,1), random(), md5(random()::text) ;

INSERT 0 100000000

 

With a select (select max(c1) from t1 group by c2;) I have this kind of output :

 

./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"

traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11

INFORMATION, no connection parameters provided, connecting to traqueur database ...

INFORMATION, connected to traqueur database

INFORMATION, PostgreSQL version : 110000

INFORMATION, sql preparation ...

INFORMATION, sql execution ...

busy_pc | distinct_exe |  pid  |  backend_type   |                query                | wait_event_type |  wait_event

---------+--------------+-------+-----------------+-------------------------------------+-----------------+--------------

      76 | 1 / 151      | 10065 | parallel worker | select max(c1) from t1 group by c2; | IO              | DataFileRead

      73 | 1 / 146      |  8262 | client backend  | select max(c1) from t1 group by c2; | IO              | DataFileRead

      72 | 1 / 144      | 10066 | parallel worker | select max(c1) from t1 group by c2; | IO              | DataFileRead

      26 | 1 / 53       | 10066 | parallel worker | select max(c1) from t1 group by c2; |                 |

      26 | 1 / 51       |  8262 | client backend  | select max(c1) from t1 group by c2; |                 |

      24 | 1 / 47       | 10065 | parallel worker | select max(c1) from t1 group by c2; |                 |

       2 | 1 / 3        | 10066 | parallel worker | select max(c1) from t1 group by c2; | IO              | BufFileWrite

       2 | 1 / 3        |  8262 | client backend  | select max(c1) from t1 group by c2; | IO              | BufFileWrite

       1 | 1 / 2        | 10065 | parallel worker | select max(c1) from t1 group by c2; | IO              | BufFileWrite

 

With an index creation (create index t1_i1 on t1(c1, c2);) I have this kind of output :

 

./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"

traqueur 2.04.00 - performance tool for PostgreSQL 9.3 => 11

INFORMATION, no connection parameters provided, connecting to traqueur database ...

INFORMATION, connected to traqueur database

INFORMATION, PostgreSQL version : 110000

INFORMATION, sql preparation ...

INFORMATION, sql execution ...

busy_pc | distinct_exe | pid  |  backend_type  |               query               | wait_event_type |  wait_event

---------+--------------+------+----------------+-----------------------------------+-----------------+--------------

      68 | 1 / 136      | 8262 | client backend | create index t1_i1 on t1(c1, c2); | IO              | DataFileRead

      26 | 1 / 53       | 8262 | client backend | create index t1_i1 on t1(c1, c2); |                 |

       6 | 1 / 11       | 8262 | client backend | create index t1_i1 on t1(c1, c2); | IO              | BufFileWrite

(3 rows)

 

 

No parallel worker. At least one parallel worker was active though, I could see its work with a direct query on pg_stat_activity or a ps -ef :

 

...

postgres  8262  8230  7 08:54 ?        00:22:46 postgres: 11/main: postgres postgres [local] CREATE INDEX

...

postgres  9833  8230 23 14:17 ?        00:00:33 postgres: 11/main: parallel worker for PID 8262

...

 

Tool only catches activity of the client backend cause column state of pg_stat_activity is null for the parallel workers in this case. I added an option to do a  "(state = 'active' or wait_event_is not null)"  It's not 100% accurate though : I miss the activity of the parallel workers which is not waiting and it’s more difficult to know who helps whom since query is also null.

I can imagine various workarounds but 11 is in devel and maybe columns active & query of pg_stat_activity will be filled for the parallel workers even for an index creation ?

 

Best regards

Phil