1/ Create tables as below ========================= CREATE TABLE T_1M (id integer, md5 text); INSERT INTO T_1M SELECT generate_series(1,1000000) AS id, md5(random()::text) AS md5; CREATE TABLE T_10M ( id integer, md5 text); INSERT INTO T_10M SELECT generate_series(1,10000000) AS id, md5(random()::text) AS md5; 2/ Start 2 psql session as below ================================ => Terminal 1, we start the monitoring psql session: [pgadm@rco ~]$ psql -d test psql (10devel) Type "help" for help. test=# => Terminal 2, we start the monitored psql session: [pgadm@rco ~]$ psql -A -d test psql (10devel) Type "help" for help. test=# Run one query: test=# \d t_10m; Table "public.t_10m" Column|Type|Collation|Nullable|Default id|integer||| md5|text||| test=# Redirect output to file to avoid blocking on psql terminal test=# \o out => Terminal 1 (Monitoring backend): get the pid of the process to be monitored: test=# select pid, query from pg_stat_activity ; pid | query -------+-------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------- 13162 | 13164 | 14609 | select pid, query from pg_stat_activity ; 14611 | SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i .inhparent = '16391' AND EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '16391') ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; 13160 | 13159 | 13161 | (7 rows) test=# Process backend with pid 14611 is the one of psql session opened in terminal 2. test=# PROGRESS 14611; PLAN PROGRESS ---------------- (1 row) test=# 3/ Run below queries in terminal 1 ================================== test=# \o out SeqScan test=# select * from t_10M order by md5; test=# select a.md5 from t_10m a, t_10m b where a.md5=b.md5 order by a.md5; test=# select a.generate_series, a.md5 from t_10m a, t_10m b where a.md5=b.md5 order by a.md5; TidScan test=# select ctid from t_10m; test=# select ctid from t_10m order by ctid; SampleScan test=# select * from t_10m tablesample system(50); test=# select * from t_10m tablesample bernouilli; Unique test=# select distinct * from t_10m; HashJoin select t_10m.id, t_1m.id, t_10m.md5 from t_10m, t_1m where t_10m.md5 = t_1m.md5; Material select * from t_10m, t_1m where t_10m.md5 like '%cb%'; Sort select * from t_10m where md5 in (select t_1m.md5 from t_1m where t_1m.id > t_10m.id order by t_1m.id); 4/ Output collected from monitoring process/backend =================================================== =>> Example 1 Terminal 1: redirect output test=# \o out Terminal 1 test=# select * from t_10M order by md5; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS ----------------------------------------------------------------------------------------- Gather Merge -> Sort=> dumping tuples to tapes rows r/w merge 0/0 rows r/w effective 0/1464520 0% Sort Key: md5 -> Parallel Seq Scan on t_10m => rows 1464520/4166700 35% blks 36011/83334 43% (5 rows) test=# =>> Example 2 Terminal 1 test=# select a.md5 from t_10m a, t_10m b where a.md5=b.md5 order by a.md5; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS --------------------------------------------------------------------------------------------- Gather Merge -> Sort=> loading tuples in memory 0 Sort Key: a.md5 -> Hash Join hashtable nbatch 256 kbytes read/write 0/345000 Hash Cond: (a.md5 = b.md5) -> Parallel Seq Scan on t_10m => rows 1/4166700 0% blks 3/83334 0% -> Hash hashtable nbatch 256 kbytes read/write 0/345000 Buckets: 65536 Batches: 256 Memory Usage: 1662kB -> Seq Scan on t_10m => rows 6711824/10000080 67% blks 55932/83334 67% (9 rows) test=# =>> Example 3 Terminal 1 test=# select ctid from t_10m order by ctid; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS --------------------------------------------------------------------------- Sort=> dumping tuples to tapes / dumping tuples rows r/w merge 0/0 rows r/w effective 0/8340801 0% Sort Key: ctid -> Seq Scan on t_10m => rows 8390890/10000080 83% blks 69924/83334 83% (4 rows) test=# =>> Example 4 Terminal 1 test=# select distinct * from t_10m; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS --------------------------------------------------------------------------- Unique -> Sort=> dumping tuples to tapes / merging tapes rows r/w merge 13626439/13626431 rows r/w effective 0/10000000 0% Sort Key: id, md5 -> Seq Scan on t_10m => rows 10000001/10000080 99% (5 rows) test=# =>> Example 5 Terminal 1 test=# select t_10m.id, t_1m.id, t_10m.md5 from t_10m, t_1m where t_10m.md5 = t_1m.md5; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS --------------------------------------------------------------------------------- Hash Join hashtable nbatch 256 kbytes read/write 0/489880 Hash Cond: (t_1m.md5 = t_10m.md5) -> Seq Scan on t_1m => rows 1/1000000 0% blks 8334/8334 100% -> Hash hashtable nbatch 256 kbytes read/write 0/489880 Buckets: 65536 Batches: 256 Memory Usage: 2337kB -> Seq Scan on t_10m => rows 8854226/10000080 88% blks 73785/83334 88% (6 rows) =>> Example 6 Terminal 1 test=# select * from t_10m, t_1m where t_10m.md5 like '%cb%'; Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS ----------------------------------------------------------------------------------- Nested Loop -> Seq Scan on t_10m => rows 5/707076 0% blks 83334/83334 100% Filter: (md5 ~~ '%cb%'::text) -> Materialize => file read readptrcount=1 tuples (write=1043690 read=3689645) -> Seq Scan on t_1m => rows 1000001/1000000 100% (5 rows) test=# =>> Example 7 Terminal 1 test=# select * from t_10m where md5 in (select t_1m.md5 from t_1m where t_1m.id > t_10m.id order by t_1m.id); Terminal 2 test=# PROGRESS 14611; PLAN PROGRESS ------------------------------------------------------------------------ Seq Scan on t_10m => rows 0/5000040 0% blks 83334/83334 100% Filter: (SubPlan 1) SubPlan 1 -> Sort=> loading tuples in memory 0 Sort Key: t_1m.id -> Seq Scan on t_1m => rows 67/333333 0% blks 4866/8334 58% Filter: (id > t_10m.id) (7 rows) test=# 5/ Command PROGRESS may be used with \watch =========================================== Terminal 1 test=# select * from t_10m, t_1m where t_10m.md5 like '%cb%'; Terminal 2 test=# \watch PROGRESS 14611; Sat 15 Apr 2017 01:00:08 AM CEST (every 1s) PLAN PROGRESS ---------------------- (1 row) ... Sat 15 Apr 2017 01:00:57 AM CEST (every 1s) PLAN PROGRESS ---------------------- (1 row) Sat 15 Apr 2017 01:00:58 AM CEST (every 1s) PLAN PROGRESS --------------------------------------------------------------------------------- Nested Loop -> Seq Scan on t_10m => rows 2/707076 0% blks 83334/83334 100% Filter: (md5 ~~ '%cb%'::text) -> Materialize => file read readptrcount=1 tuples (write=1043690 read=49899) -> Seq Scan on t_1m => rows 1000001/1000000 100% (5 rows) Sat 15 Apr 2017 01:00:59 AM CEST (every 1s) PLAN PROGRESS ----------------------------------------------------------------------------------- Nested Loop -> Seq Scan on t_10m => rows 3/707076 0% blks 83334/83334 100% Filter: (md5 ~~ '%cb%'::text) -> Materialize => file read readptrcount=1 tuples (write=1043690 read=1280523) -> Seq Scan on t_1m => rows 1000001/1000000 100% (5 rows) Sat 15 Apr 2017 01:01:00 AM CEST (every 1s) PLAN PROGRESS ----------------------------------------------------------------------------------- Nested Loop -> Seq Scan on t_10m => rows 4/707076 0% blks 83334/83334 100% Filter: (md5 ~~ '%cb%'::text) -> Materialize => file read readptrcount=1 tuples (write=1043690 read=2494272) -> Seq Scan on t_1m => rows 1000001/1000000 100% (5 rows) Sat 15 Apr 2017 01:01:01 AM CEST (every 1s) ...