Hi Shijia, If you're using fish, I suspect you're on a Mac - I don't have experience on this platform.
Can you check with pgAdmin (3 or 4) what the server is busy doing after a few iterations? Check for locks, as it could be a cause. Also, do you have concurrent INSERTs? Olivier On Mon, Dec 16, 2019, 10:52 Shijia Wei <shijia...@utexas.edu> wrote: > Hi Olivier, > > I do not think that the queries are executed concurrently. The bash for > loop ensures that the next command fires only after the first returns. > Also for some 'complex' queries, even a wait-period that is longer than > the total execution time does not completely avoid this effect. > For example, a wait-period of 5-second in between queries that take > 2-second to run, does not help avoid the increasing runtime problem > completely. > > Thanks, > Shijia > > > On Mon, Dec 16, 2019 at 2:04 AM Olivier Gautherot < > ogauthe...@gautherot.net> wrote: > >> Hi Shijia, >> >> It sounds like concurrency on the queries: the second starts before the >> first ends, and so on. With a short wait in between you ensure sequential >> execution. Notice that you also have the overhead of concurrent psql... >> >> Sounds normal to me. >> >> Best regards >> Olivier >> >> >> On Mon, Dec 16, 2019, 07:00 Shijia Wei <shijia...@utexas.edu> wrote: >> >>> Hi all, >>> >>> I am running TPC-H on recent postgresql (12.0 and 12.1). >>> On some of the queries (that may involve parallel scans) I see this >>> interesting behavior: >>> When these queries are executed back-to-back (sent from psql >>> interactive terminal), the total execution time of them >>> increase monotonically. >>> >>> I simplified query-1 to demonstrate this effect: >>> ``` example.sql >>> explain (analyze, buffers) select >>> max(l_shipdate) as max_data, >>> count(*) as count_order >>> from >>> lineitem >>> where >>> l_shipdate <= date '1998-12-01' - interval '20' day; >>> ``` >>> >>> When I execute (from fish) following command: >>> `for i in (seq 1 20); psql tpch < example.sql | grep Execution; end` >>> The results I get are as follows: >>> " >>> Execution Time: 184.864 ms >>> Execution Time: 192.758 ms >>> Execution Time: 197.380 ms >>> Execution Time: 200.384 ms >>> Execution Time: 202.950 ms >>> Execution Time: 205.695 ms >>> Execution Time: 208.082 ms >>> Execution Time: 209.108 ms >>> Execution Time: 212.428 ms >>> Execution Time: 214.539 ms >>> Execution Time: 215.799 ms >>> Execution Time: 219.057 ms >>> Execution Time: 222.102 ms >>> Execution Time: 223.779 ms >>> Execution Time: 227.819 ms >>> Execution Time: 229.710 ms >>> Execution Time: 239.439 ms >>> Execution Time: 237.649 ms >>> Execution Time: 249.178 ms >>> Execution Time: 261.268 ms >>> " >>> In addition, if the repeated more times, the total execution time can >>> end up being 10X and more!!! >>> >>> When there a wait period in-between queries, (e.g. sleep 10) in the >>> above for loop, this increasing execution time behavior goes a way. >>> For more complex queries, the "wait period" needs to be longer to avoid >>> the increase in execution time. >>> >>> Some metadata about this table "lineitem": >>> tpch=# \d lineitem >>> Table "public.lineitem" >>> Column | Type | Collation | Nullable | >>> Default >>> >>> -----------------+-----------------------+-----------+----------+--------- >>> l_orderkey | integer | | not null | >>> l_partkey | integer | | not null | >>> l_suppkey | integer | | not null | >>> l_linenumber | integer | | not null | >>> l_quantity | numeric(15,2) | | not null | >>> l_extendedprice | numeric(15,2) | | not null | >>> l_discount | numeric(15,2) | | not null | >>> l_tax | numeric(15,2) | | not null | >>> l_returnflag | character(1) | | not null | >>> l_linestatus | character(1) | | not null | >>> l_shipdate | date | | not null | >>> l_commitdate | date | | not null | >>> l_receiptdate | date | | not null | >>> l_shipinstruct | character(25) | | not null | >>> l_shipmode | character(10) | | not null | >>> l_comment | character varying(44) | | not null | >>> Indexes: >>> "i_l_commitdate" btree (l_commitdate) >>> "i_l_orderkey" btree (l_orderkey) >>> "i_l_orderkey_quantity" btree (l_orderkey, l_quantity) >>> "i_l_partkey" btree (l_partkey) >>> "i_l_receiptdate" btree (l_receiptdate) >>> "i_l_shipdate" btree (l_shipdate) >>> "i_l_suppkey" btree (l_suppkey) >>> "i_l_suppkey_partkey" btree (l_partkey, l_suppkey) >>> >>> tpch=# SELECT relname, relpages, reltuples, relallvisible, relkind, >>> relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class >>> WHERE relname='lineitem'; >>> relname | relpages | reltuples | relallvisible | relkind | relnatts >>> | relhassubclass | reloptions | pg_table_size >>> >>> ----------+----------+--------------+---------------+---------+----------+----------------+------------+--------------- >>> lineitem | 112503 | 6.001167e+06 | 112503 | r | >>> 16 | f | | 921903104 >>> (1 row) >>> >>> Postgresql 12.0 and 12.1 are all manually installed from source. >>> Both are running on Ubuntu 16.04 kernel 4.4.0-142-generic, on Intel(R) >>> Core(TM) i7-6700K. >>> >>> >>> Any help greatly appreciated! >>> >>> Shijia >>> >> > > -- > *Shijia Wei* > ECE, UT Austin | ACSES | 3rd Year PhD > shijia...@utexas.edu | https://0x161e-swei.github.io >
