Re: Consecutive Query Executions with Increasing Execution Time
Olivier Gautherot <[email protected]> Mon, 16 Dec 2019 11:17:50 +0100
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CAJ7S9TVc2vNPWZb6wKR2UmiUTheyGdJjexvcxinZw16D2uHj7w@mail.gmail.com> |
--0000000000007a670a0599cf862b Content-Type: text/plain; charset="UTF-8" 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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> 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 > [email protected] | https://0x161e-swei.github.io > --0000000000007a670a0599cf862b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"auto">Hi Shijia,<div dir=3D"auto"><br></div><div dir=3D"auto">I= f you're using fish, I suspect you're on a Mac - I don't have e= xperience on this platform.</div><div dir=3D"auto"><br><div dir=3D"auto">Ca= n 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 con= current INSERTs?</div><div dir=3D"auto"><br></div><div dir=3D"auto">Olivier= </div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"= gmail_attr">On Mon, Dec 16, 2019, 10:52 Shijia Wei <<a href=3D"mailto:sh= [email protected]">[email protected]</a>> wrote:<br></div><blockquot= e class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc sol= id;padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D= "font-family:arial,helvetica,sans-serif">Hi=C2=A0Olivier,</div><div class= =3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></d= iv><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-s= erif">I do not think that=C2=A0the queries are executed concurrently. The b= ash for loop ensures that the next command fires only after the first retur= ns.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,= sans-serif">Also for some 'complex' queries, even a wait-period tha= t is longer than the total execution time does not completely avoid this ef= fect.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetic= a,sans-serif">For example, a wait-period of 5-second in between=C2=A0querie= s that take 2-second to run, does not help avoid the increasing <span id=3D= "m_-6514454030893449159:11c.1">runtime</span> problem completely.</div><div= class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><= br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,= sans-serif">Thanks,</div><div class=3D"gmail_default" style=3D"font-family:= arial,helvetica,sans-serif"><span id=3D"m_-6514454030893449159:11c.2">Shiji= a</span></div><div class=3D"gmail_default" style=3D"font-family:arial,helve= tica,sans-serif"><br></div></div><br><div class=3D"gmail_quote"><div dir=3D= "ltr" class=3D"gmail_attr">On Mon, Dec 16, 2019 at 2:04 AM Olivier Gauthero= t <<a href=3D"mailto:[email protected]" target=3D"_blank" rel=3D"= noreferrer">[email protected]</a>> wrote:<br></div><blockquote cl= ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid= rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div>Hi Shijia,<br><b= r>It sounds like concurrency on the queries: the second starts before the f= irst ends, and so on. With a short wait in between you ensure sequential ex= ecution. Notice that you also have the overhead of concurrent psql...</div>= <div dir=3D"auto"><br></div><div dir=3D"auto">Sounds normal to me.</div><di= v dir=3D"auto"><br></div><div dir=3D"auto">Best regards</div><div dir=3D"au= to">Olivier</div><div dir=3D"auto"><br></div><div dir=3D"auto"><br><div cla= ss=3D"gmail_quote" dir=3D"auto"><div dir=3D"ltr" class=3D"gmail_attr">On Mo= n, Dec 16, 2019, 07:00 Shijia Wei <<a href=3D"mailto:[email protected]= u" target=3D"_blank" rel=3D"noreferrer">[email protected]</a>> wrote:= <br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8= ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr= "><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-se= rif">Hi all,</div><div class=3D"gmail_default" style=3D"font-family:arial,h= elvetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-f= amily:arial,helvetica,sans-serif">I am running <span id=3D"m_-6514454030893= 449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601275695= 23798833:1wh.1">TPC</span>-H on recent <span id=3D"m_-6514454030893449159gm= ail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833= :1wh.2">postgresql</span> (12.0 and 12.1).</div><div class=3D"gmail_default= " style=3D"font-family:arial,helvetica,sans-serif">On some of the queries (= that may involve parallel scans) I see this interesting behavior:</div><div= class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">W= hen these queries are executed back-to-back (sent from <span id=3D"m_-65144= 54030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_496= 0127569523798833:1wh.3">psql</span> interactive terminal), the total execut= ion time of them increase=C2=A0monotonically.</div><div class=3D"gmail_defa= ult" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div class= =3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">I simpl= ified query-1 to demonstrate this effect:</div><div class=3D"gmail_default"= ><font face=3D"monospace">``` example.<span id=3D"m_-6514454030893449159gma= il-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:= 1wh.4">sql</span></font></div><div class=3D"gmail_default"><font face=3D"mo= nospace">explain (analyze, buffers) select<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 m= ax(l_<span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_= -8756800177654151280m_4960127569523798833:1wh.5">shipdate</span>) as max_da= ta,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 count(*) as count_order<br>from<br>=C2= =A0 =C2=A0 =C2=A0 =C2=A0 <span id=3D"m_-6514454030893449159gmail-m_-5795010= 130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.6">lineit= em</span><br>where<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 l_<span id=3D"m_-65144540= 30893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_496012= 7569523798833:1wh.7">shipdate</span> <=3D date '1998-12-01' - in= terval '20' day;<br>```</font></div><div class=3D"gmail_default"><f= ont face=3D"monospace"><br></font></div><div class=3D"gmail_default"><font = face=3D"arial, helvetica, sans-serif">When I execute (from fish) following = command:</font><br><font face=3D"monospace">`for i in (seq 1 20); <span id= =3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680017765= 4151280m_4960127569523798833:1wh.8">psql</span> <span id=3D"m_-651445403089= 3449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569= 523798833:1wh.9">tpch</span> < example.<span id=3D"m_-651445403089344915= 9gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798= 833:1wh.10">sql</span> | grep Execution; end`</font><br><font face=3D"arial= , helvetica, sans-serif">The results I get are as follows:</font><br><font = face=3D"arial, helvetica, sans-serif">"</font><br><font face=3D"arial,= helvetica, sans-serif">=C2=A0Execution Time: 184.864 ms</font><br><font fa= ce=3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 192.758 ms</font>= <br><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 197.3= 80 ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution= Time: 200.384 ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2= =A0Execution Time: 202.950 ms</font><br><font face=3D"arial, helvetica, san= s-serif">=C2=A0Execution Time: 205.695 ms</font><br><font face=3D"arial, he= lvetica, sans-serif">=C2=A0Execution Time: 208.082 ms</font><br><font face= =3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 209.108 ms</font><b= r><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 212.428= ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution T= ime: 214.539 ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2= =A0Execution Time: 215.799 ms</font><br><font face=3D"arial, helvetica, san= s-serif">=C2=A0Execution Time: 219.057 ms</font><br><font face=3D"arial, he= lvetica, sans-serif">=C2=A0Execution Time: 222.102 ms</font><br><font face= =3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 223.779 ms</font><b= r><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 227.819= ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2=A0Execution T= ime: 229.710 ms</font><br><font face=3D"arial, helvetica, sans-serif">=C2= =A0Execution Time: 239.439 ms</font><br><font face=3D"arial, helvetica, san= s-serif">=C2=A0Execution Time: 237.649 ms</font><br><font face=3D"arial, he= lvetica, sans-serif">=C2=A0Execution Time: 249.178 ms</font><br><font face= =3D"arial, helvetica, sans-serif">=C2=A0Execution Time: 261.268 ms</font><b= r><font face=3D"arial, helvetica, sans-serif">"</font></div><div class= =3D"gmail_default"><font face=3D"arial, helvetica, sans-serif">In addition,= if the repeated more times, the total execution time can end up being 10X = and more!!!</font></div><div class=3D"gmail_default"><font face=3D"arial, h= elvetica, sans-serif"><br></font></div><div class=3D"gmail_default"><font f= ace=3D"arial, helvetica, sans-serif">When there a wait period in-between qu= eries, (e.g. </font><font face=3D"monospace">sleep 10</font><font face=3D"a= rial, helvetica, sans-serif">) in the above for loop, this increasing execu= tion time behavior goes a way.</font></div><div class=3D"gmail_default"><fo= nt face=3D"arial, helvetica, sans-serif">For more complex queries, the &quo= t;wait period" needs to be longer to avoid the increase in execution t= ime.</font></div><div class=3D"gmail_default" style=3D"font-family:arial,he= lvetica,sans-serif"><br>Some <span id=3D"m_-6514454030893449159gmail-m_-579= 5010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.11">m= etadata</span> about this table "<span id=3D"m_-6514454030893449159gma= il-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:= 1wh.12">lineitem</span>":</div><div class=3D"gmail_default"><font face= =3D"monospace"><span id=3D"m_-6514454030893449159gmail-m_-57950101301504948= 50gmail-m_-8756800177654151280m_4960127569523798833:1wh.13">tpch</span>=3D#= \d <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-= 8756800177654151280m_4960127569523798833:1wh.14">lineitem</span><br>=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Table "public.<span id=3D"m_-6514454030893449159gmail-m_-579= 5010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.15">l= ineitem</span>"<br>=C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Collat= ion | <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m= _-8756800177654151280m_4960127569523798833:1wh.16">Nullable</span> | Defaul= t<br>-----------------+-----------------------+-----------+----------+-----= ----<br>=C2=A0l_<span id=3D"m_-6514454030893449159gmail-m_-5795010130150494= 850gmail-m_-8756800177654151280m_4960127569523798833:1wh.17">orderkey</span= > =C2=A0 =C2=A0 =C2=A0| integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span i= d=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-87568001776= 54151280m_4960127569523798833:1wh.18">partkey</span> =C2=A0 =C2=A0 =C2=A0 |= integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span id=3D"m_-651445403089344= 9159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523= 798833:1wh.19">suppkey</span> =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | n= ot null |<br>=C2=A0l_<span id=3D"m_-6514454030893449159gmail-m_-57950101301= 50494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.20">linenumbe= r</span> =C2=A0 =C2=A0| integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_quantit= y =C2=A0 =C2=A0 =C2=A0| numeric(15,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span id=3D"m_-65144540= 30893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_496012= 7569523798833:1wh.21">extendedprice</span> | numeric(15,2) =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_= discount =C2=A0 =C2=A0 =C2=A0| numeric(15,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_tax =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | numeric(15,2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span id=3D"m_-6514= 454030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49= 60127569523798833:1wh.22">returnflag</span> =C2=A0 =C2=A0| character(1) =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not n= ull |<br>=C2=A0l_<span id=3D"m_-6514454030893449159gmail-m_-579501013015049= 4850gmail-m_-8756800177654151280m_4960127569523798833:1wh.23">linestatus</s= pan> =C2=A0 =C2=A0| character(1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span id=3D"m_-6514454= 030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601= 27569523798833:1wh.24">shipdate</span> =C2=A0 =C2=A0 =C2=A0| date =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_<span id=3D"m_-651445403089344915= 9gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798= 833:1wh.25">commitdate</span> =C2=A0 =C2=A0| date =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | not null |<br>=C2=A0l_<span id=3D"m_-6514454030893449159gmail-m_-57950101= 30150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.26">receip= tdate</span> =C2=A0 | date =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_= <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-8756= 800177654151280m_4960127569523798833:1wh.27">shipinstruct</span> =C2=A0| ch= aracter(25) =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | not null |<br>=C2=A0l_<span id=3D"m_-6514454030893449159gmail-m_-5795= 010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.28">sh= ipmode</span> =C2=A0 =C2=A0 =C2=A0| character(10) =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |<br>=C2=A0l_comment = =C2=A0 =C2=A0 =C2=A0 | character varying(44) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | not null |<br>Indexes:<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-6= 514454030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m= _4960127569523798833:1wh.29">commitdate</span>" <span id=3D"m_-6514454= 030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601= 27569523798833:1wh.30">btree</span> (l_<span id=3D"m_-6514454030893449159gm= ail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833= :1wh.31">commitdate</span>)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-65144= 54030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_496= 0127569523798833:1wh.32">orderkey</span>" <span id=3D"m_-6514454030893= 449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601275695= 23798833:1wh.33">btree</span> (l_<span id=3D"m_-6514454030893449159gmail-m_= -5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.3= 4">orderkey</span>)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-6514454030893= 449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601275695= 23798833:1wh.35">orderkey</span>_quantity" <span id=3D"m_-651445403089= 3449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569= 523798833:1wh.36">btree</span> (l_<span id=3D"m_-6514454030893449159gmail-m= _-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.= 37">orderkey</span>, l_quantity)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-= 6514454030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280= m_4960127569523798833:1wh.38">partkey</span>" <span id=3D"m_-651445403= 0893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127= 569523798833:1wh.39">btree</span> (l_<span id=3D"m_-6514454030893449159gmai= l-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1= wh.40">partkey</span>)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-6514454030= 893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601275= 69523798833:1wh.41">receiptdate</span>" <span id=3D"m_-651445403089344= 9159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523= 798833:1wh.42">btree</span> (l_<span id=3D"m_-6514454030893449159gmail-m_-5= 795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.43"= >receiptdate</span>)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-651445403089= 3449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569= 523798833:1wh.44">shipdate</span>" <span id=3D"m_-6514454030893449159g= mail-m_-5795010130150494850gmail-m_-8756800177654151280m_496012756952379883= 3:1wh.45">btree</span> (l_<span id=3D"m_-6514454030893449159gmail-m_-579501= 0130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.46">ship= date</span>)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-6514454030893449159g= mail-m_-5795010130150494850gmail-m_-8756800177654151280m_496012756952379883= 3:1wh.47">suppkey</span>" <span id=3D"m_-6514454030893449159gmail-m_-5= 795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.48"= >btree</span> (l_<span id=3D"m_-6514454030893449159gmail-m_-579501013015049= 4850gmail-m_-8756800177654151280m_4960127569523798833:1wh.49">suppkey</span= >)<br>=C2=A0 =C2=A0 "i_l_<span id=3D"m_-6514454030893449159gmail-m_-57= 95010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.50">= suppkey</span>_<span id=3D"m_-6514454030893449159gmail-m_-57950101301504948= 50gmail-m_-8756800177654151280m_4960127569523798833:1wh.51">partkey</span>&= quot; <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m= _-8756800177654151280m_4960127569523798833:1wh.52">btree</span> (l_<span id= =3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680017765= 4151280m_4960127569523798833:1wh.53">partkey</span>, l_<span id=3D"m_-65144= 54030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_496= 0127569523798833:1wh.54">suppkey</span>)<br></font></div><div class=3D"gmai= l_default"><font face=3D"monospace"><br></font></div><div class=3D"gmail_de= fault"><font face=3D"monospace"><span id=3D"m_-6514454030893449159gmail-m_-= 5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.55= ">tpch</span>=3D# SELECT <span id=3D"m_-6514454030893449159gmail-m_-5795010= 130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.56">relna= me</span>, <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gm= ail-m_-8756800177654151280m_4960127569523798833:1wh.57">relpages</span>, <s= pan id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680= 0177654151280m_4960127569523798833:1wh.58">reltuples</span>, <span id=3D"m_= -6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680017765415128= 0m_4960127569523798833:1wh.59">relallvisible</span>, <span id=3D"m_-6514454= 030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601= 27569523798833:1wh.60">relkind</span>, <span id=3D"m_-6514454030893449159gm= ail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833= :1wh.61">relnatts</span>, <span id=3D"m_-6514454030893449159gmail-m_-579501= 0130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.62">relh= assubclass</span>, <span id=3D"m_-6514454030893449159gmail-m_-5795010130150= 494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.63">reloptions<= /span>, pg_table_size(<span id=3D"m_-6514454030893449159gmail-m_-5795010130= 150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.64">oid</spa= n>) FROM pg_class WHERE <span id=3D"m_-6514454030893449159gmail-m_-57950101= 30150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.65">relnam= e</span>=3D'lineitem';<br>=C2=A0<span id=3D"m_-6514454030893449159g= mail-m_-5795010130150494850gmail-m_-8756800177654151280m_496012756952379883= 3:1wh.66">relname</span> =C2=A0| <span id=3D"m_-6514454030893449159gmail-m_= -5795010130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.6= 7">relpages</span> | =C2=A0<span id=3D"m_-6514454030893449159gmail-m_-57950= 10130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.68">rel= tuples</span> =C2=A0 | <span id=3D"m_-6514454030893449159gmail-m_-579501013= 0150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.69">relallv= isible</span> | <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494= 850gmail-m_-8756800177654151280m_4960127569523798833:1wh.70">relkind</span>= | <span id=3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-8= 756800177654151280m_4960127569523798833:1wh.71">relnatts</span> | <span id= =3D"m_-6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680017765= 4151280m_4960127569523798833:1wh.72">relhassubclass</span> | <span id=3D"m_= -6514454030893449159gmail-m_-5795010130150494850gmail-m_-875680017765415128= 0m_4960127569523798833:1wh.73">reloptions</span> | pg_table_size<br>-------= ---+----------+--------------+---------------+---------+----------+--------= --------+------------+---------------<br>=C2=A0<span id=3D"m_-6514454030893= 449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_49601275695= 23798833:1wh.74">lineitem</span> | =C2=A0 112503 | 6.001167e+06 | =C2=A0 = =C2=A0 =C2=A0 =C2=A0112503 | r =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = 16 | f =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 921903104<br>(1 row)</font><br></di= v><div class=3D"gmail_default"><font face=3D"monospace"><br></font></div><d= iv class=3D"gmail_default"><font face=3D"arial, sans-serif"><span id=3D"m_-= 6514454030893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280= m_4960127569523798833:1wh.75">Postgresql</span> 12.0 and 12.1 are all=C2=A0= </font>manually installed from source.</div><div class=3D"gmail_default"><f= ont face=3D"arial, sans-serif">Both are running on <span id=3D"m_-651445403= 0893449159gmail-m_-5795010130150494850gmail-m_-8756800177654151280m_4960127= 569523798833:1wh.76">Ubuntu</span> 16.04 kernel=C2=A0</font>4.4.0-142-gener= ic, on Intel(R) Core(TM) i7-6700K.</div><div class=3D"gmail_default"><br></= div><div class=3D"gmail_default"><br></div><div class=3D"gmail_default">Any= help greatly appreciated!</div><div class=3D"gmail_default"><br></div><div= class=3D"gmail_default"><span id=3D"m_-6514454030893449159gmail-m_-5795010= 130150494850gmail-m_-8756800177654151280m_4960127569523798833:1wh.77">Shiji= a</span></div></div> </blockquote></div></div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= ><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir= =3D"ltr"><div><div dir=3D"ltr"><div dir=3D"ltr"><font size=3D"2" style=3D"c= olor:rgb(136,136,136)"><font size=3D"1" face=3D"Verdana"><b>Shijia Wei</b><= br>ECE,=C2=A0</font></font><span style=3D"color:rgb(136,136,136);font-famil= y:Verdana;font-size:x-small">UT Austin</span><span style=3D"font-family:Ver= dana;font-size:x-small;color:rgb(136,136,136)">=C2=A0| ACSES | 3rd Year PhD= </span></div><div dir=3D"ltr"><font size=3D"2" style=3D"color:rgb(136,136,1= 36)"><font size=3D"1" face=3D"Verdana"><span><a href=3D"mailto:shijiawei@ut= exas.edu" target=3D"_blank" rel=3D"noreferrer">[email protected]</a></sp= an>=C2=A0|=C2=A0</font></font><font color=3D"#888888" face=3D"Verdana" size= =3D"1"><a href=3D"https://0x161e-swei.github.io" target=3D"_blank" rel=3D"n= oreferrer">https://0x161e-swei.github.io</a></font></div></div></div></div>= </div></div></div></div></div></div></div> </blockquote></div> --0000000000007a670a0599cf862b--