Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0P5Z-0008Py-7m for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Sep 2018 10:43:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g0P5X-0008DI-6a for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Sep 2018 10:43:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0OgF-0004Gi-Dg for pgsql-bugs@lists.postgresql.org; Thu, 13 Sep 2018 10:16:59 +0000 Received: from mx1.datakom.lv ([158.255.56.65]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0Og8-000668-2N for pgsql-bugs@postgresql.org; Thu, 13 Sep 2018 10:16:58 +0000 X-AuditID: c0a8dc08-113ff700000011f4-6a-5b9a39138c6c Received: from mx1.datakom.lv (Unknown_Domain [192.168.220.25]) (using TLS with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mx1.datakom.lv (SMTP) with SMTP id 15.32.04596.3193A9B5; Thu, 13 Sep 2018 13:16:51 +0300 (EEST) Received: from SRVEX01.datakom.lv (192.168.220.25) by SRVEX01.datakom.lv (192.168.220.25) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1531.3; Thu, 13 Sep 2018 13:19:04 +0300 Received: from SRVEX01.datakom.lv ([fe80::5d31:8f68:4138:7629]) by SRVEX01.datakom.lv ([fe80::5d31:8f68:4138:7629%5]) with mapi id 15.01.1531.003; Thu, 13 Sep 2018 13:19:04 +0300 From: =?windows-1257?Q?Mareks_Kalna=E8s?= To: Tom Lane , Victor Yegorov CC: "pgsql-bugs@postgresql.org" , =?windows-1257?Q?M=E2ris_Rucis?= , =?windows-1257?Q?P=E2vels_Kor=FEs?= Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem Thread-Topic: PostgreSQL 10.0 SELECT LIMIT performance problem Thread-Index: AdRKcrcxKsJ4mGzUR4mNVsCT/UUnVQACLeMAAAfHS+AAAQD+AAAAureAACnf/cA= Date: Thu, 13 Sep 2018 10:19:04 +0000 Message-ID: References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv> <9924.1536771851@sss.pgh.pa.us> In-Reply-To: <9924.1536771851@sss.pgh.pa.us> Accept-Language: en-GB, lv-LV, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.230.57] Content-Type: multipart/alternative; boundary="_000_c081b34fc8584e4e80ddf0e99eabbba5datakomlv_" MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFlrBIsWRmVeSWpSXmKPExsVyYMUdSV1hy1nRBs2HlC0u9M5ltji16i6T xb2d69kcmD12zrrL7nFh3zR2j0XPNrIEMEdx2aSk5mSWpRbp2yVwZfx994ep4OVExopzu48z NjCeqO1i5OSQEDCRmHt3LmMXIxeHkMAkJonbE/uYIJyXjBI/jqyAcrYxStzpWMfSxcjBwSbg KvG+QwGkWwTI/Dm5kRmkhllgJ6PEjJt7mUESwgK2Ehv7rjJCFNlJzJ+5mR3C9pOYPX83C4jN IqAq8WHORFYQm1fAUmLf9PvMEMtmMkl0X94AVsQpoCNxb+IrsGZGARWJd193gtnMAuISt57M Z4L4QUBiyZ7zzBC2qMTLx/9YIWwlibNvDjFB1CdKLLnyFWqZoMTJmU9YJjCKzkIyahaSsllI yiDiBhIHzixlhLC1JZYtfM0MYetLfD7VwIosvoCRfRUjf3FuuqFeSmJJYnZ+rl5O2SZGSPxx 7GC89iXwEKMAB6MSD+8D6ZnRQqyJZcWVuYcYJTiYlUR4X7MDhXhTEiurUovy44tKc1KLDzFK c7AoifP+bSiLFhJITyxJzU5NLUgtgskycXBKNTDyRnx+mHPA25eH513g32q5Ey3nFCS/XhP3 DPPPzjPIt0vV+PDtI8Ozpf/mya1M+Ra3UlqAJWrOGQ2Jmw5+/5Yaxf4znMh3/SQf9xxezXeZ Oy8qXD4Yp3ZIY93nJvNvb7PXaN5e9rxjwauOw7seBIu3Ptn7i3Hnu6u/PDdHX/0zR+2qbWmy dEanEktxRqKhFnNRcSIAWkaZnLsCAAA= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_c081b34fc8584e4e80ddf0e99eabbba5datakomlv_ Content-Type: text/plain; charset="windows-1257" Content-Transfer-Encoding: quoted-printable Hi! My point is, why planned ignore table hits and, when joining tables, think = that rows will be less than in reality: Table1: Index Scan using idx_tvc_entity_sid_u on tvcis.tvc_entity e (cost= =3D0.28..1856.72 rows=3D253 width=3D208) (actual time=3D0.091..13.944 rows= =3D71 loops=3D1) Table2: Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost= =3D0.29..16.84 rows=3D23 width=3D951) (actual time=3D0.009..0.033 rows=3D18= loops=3D71) Join: Nested Loop (cost=3D0.57..6175.55 rows=3D9 width=3D951) (actual time= =3D0.112..17.355 rows=3D1291 loops=3D1) Why planned don=92t take in account results from each table: Join rows =3D Table1 rows * Table2 rows =3D=3D 253 * 23 =3D 5819? In this c= ase difference will be 22%, but planner got 9 rows what is 0.7% from actual= result As we see, actual rows almost equal with subquery row multiplication 71 * 1= 8 ~=3D 1291 Mareks -----Original Message----- From: Tom Lane Sent: tre=F0diena, 2018. gada 12. septembris 20:04 To: Victor Yegorov Cc: Mareks Kalna=E8s ; pgsql-bugs@postgresql.org= ; M=E2ris Rucis Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem Victor Yegorov > writes: > The fact, that planner is not accurate on the estimates of JSON > internal keys is expected, PostgreSQL is not parsing JSON values when > gathering stats. > You cannot expect planner to be picky about all possible corner cases, > it would make planning time enormously huge. Right. The fact that it doesn't make the right guesses without help can't = be considered to be a bug in all cases. These are engineering tradeoffs we= have to make. > That is the reason I outlined, that important keys should be extracted > into plain columns. If that seems infeasible from an application standpoint, another possibilit= y is to make expression indexes on those important keys. ANALYZE will gather stats on the values of indexed expressions, and then pe= rhaps the planner will have enough info to make better decisions. In the other case mentioned, where the problem is a poor guess about the se= lectivity of where ( to_tsvector( 'simple'::regconfig, a.ts_vector_fields ) @@ to_tsquery( 'simple', ' (decodedType5b6f0753ossAl= armsGenerator) ' ) ) it's the same problem: the planner has no stats that would let it figure ou= t the selectivity. It can't reasonably extract an estimate on the fly --- = if it did, you'd be complaining that planning time was too long. The only = way to get reasonable behavior is to set things up so that ANALYZE will acc= umulate stats about the values of "to_tsvector('simple',a.ts_vector_fields)= ". You can arrange that either by extracting that into a column, or by mak= ing an index on it. regards, tom lane ________________________________ =D0is elektronisk=E2 pasta s=FBt=EEjums un taj=E2 eso=F0ie dokumenti ir par= edz=E7ts tikai nor=E2d=EEtajam adres=E2tam(-iem). Tas satur konfidenci=E2lu= inform=E2ciju un var satur=E7t ar=EE privili=EC=E7tu inform=E2ciju. T=E2 s= atura piln=EEga vai da=EF=E7ja nesankcion=E7ta izpau=F0ana, izmanto=F0ana v= ai t=E2l=E2ka izplat=EE=F0ana jebk=E2d=E2 veid=E2 ir aizliegta. Ja =F0is el= ektronisk=E2 pasta s=FBt=EEjums sa=F2emts k=EF=FBdas d=E7=EF, l=FBdzam sazi= n=E2ties ar s=FBt=EEt=E2ju, nos=FBtot atbildes e-pasta v=E7stuli, un izdz= =E7st =F0o v=E7stuli. Dro=F0=EEbas apsv=E7rumu d=E7=EF SIA Datakom var veik= t elektronisk=E2 pasta s=FBt=EEjumu un tiem pievienoto dokumentu satura mon= itoringu. Ja vien =F0aj=E2 v=E7stul=E7 vai pievienotajos dokumentus nav nep= =E2rprotami teikts pret=E7jais, =F0is elektronisk=E2 pasta s=FBt=EEjums vai= tam pievienotie dokumenti nav uzskat=E2mi par SIA Datakom pied=E2v=E2jumu = sl=E7gt dar=EEjumu, ap=F2em=F0anos vai gatav=EEbu uz=F2emties saist=EEbas. = S=FBt=EEt=E2js: SIA Datakom, vienotais re=ECistr=E2cijas nr: 40103142605, J= uridisk=E2 adrese: Vien=EEbas gatve 109, R=EEga, LV-1058, Latvija This electronic transmission and attached documents is intended only for th= e addressee(s). It contains confidential information and may also contain p= rivileged information. If you are not an intended recipient, you may not us= e, read, retransmit, disseminate or take any action in reliance upon it. If= this electronic transmission is received in error, please contact the send= er by sending a reply e-mail and delete this message. SIA Datakom reserves = the right to monitor all e-mail communications through its networks. Unless= this letter or the accompanying documents clearly stated otherwise, by thi= s electronic transmission does not constitute a contract offer, a contract = amendment or an acceptance of a contract offer on behalf of the SIA Datakom= . Sender: SIA Datakom, registration number: 40103142605, Latvia. Registered= office: Vienibas gatve 109, Riga, LV-1058, Latvia ________________________________ --_000_c081b34fc8584e4e80ddf0e99eabbba5datakomlv_ Content-Type: text/html; charset="windows-1257" Content-Transfer-Encoding: quoted-printable

Hi!

 

My point is, why planned ignore table hits and, w= hen joining tables, think that rows will be less than in reality:

Table1: Index Scan using idx_tvc_entity_si= d_u on tvcis.tvc_entity e  (cost=3D0.28..1856.72 rows=3D253 width=3D208) (actual time=3D0.091..13.944 rows=3D71 loops=3D1)

Table2: Index Scan using idx_oss_objects_s= id on tvcis.oss_objects o  (cost=3D0.29..16.84 rows=3D23= width=3D951) (actual time=3D0.009..0.033 rows=3D18 loops=3D71)

Join: Nested Loop  (cost=3D0.57..6175= .55 rows=3D9 width=3D951) (actual time=3D0.112..17.355 rows=3D1291 loops=3D1)

 

Why planned don=92t take in account results from = each table:

Join rows =3D Table1 rows * Table2 rows =3D=3D 253 * 23 =3D 5819? In this case differenc= e will be 22%, but planner got 9 rows what is 0.7% from actual result<= /o:p>

As we see, actual rows almost equal with subquery= row multiplication = 71 * 18 ~=3D 1291

 

Mareks

 

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: tre=F0diena, 2018. gada 12. septembris 20:04
To: Victor Yegorov <vyegorov@gmail.com>
Cc: Mareks Kalna=E8s <Mareks.Kalnacs@datakom.lv>; pgsql-bugs@postgres= ql.org; M=E2ris Rucis <Maris.Rucis@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem

 

Victor Yegorov <vyegorov@gmail.com> writes:

> The fact, that planner is = not accurate on the estimates of JSON

> internal keys is expected,= PostgreSQL is not parsing JSON values when

> gathering stats.

> You cannot expect planner = to be picky about all possible corner cases,

> it would make planning tim= e enormously huge.

 

Right.  The fact that it d= oesn't make the right guesses without help can't be considered to be a bug = in all cases.  These are engineering tradeoffs we have to make.

 

> That is the reason I outli= ned, that important keys should be extracted

> into plain columns.

 

If that seems infeasible from a= n application standpoint, another possibility is to make expression indexes= on those important keys.

ANALYZE will gather stats on th= e values of indexed expressions, and then perhaps the planner will have eno= ugh info to make better decisions.

 

In the other case mentioned, wh= ere the problem is a poor guess about the selectivity of<= /p>

 

where (

     &= nbsp;           &nbs= p;            &= nbsp; to_tsvector(

     &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;     'simple'::regconfig,

     &= nbsp;           &nbs= p;             =             &nb= sp;    a.ts_vector_fields

     &= nbsp;           &nbs= p;            &= nbsp; ) @@ to_tsquery(

     &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;     'simple',

     &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;     ' (decodedType5b6f0753ossAlarmsGenerator) '=

     &= nbsp;            &nb= sp;            =  )

)

 

it's the same problem: the plan= ner has no stats that would let it figure out the selectivity.  It can= 't reasonably extract an estimate on the fly --- if it did, you'd be compla= ining that planning time was too long.  The only way to get reasonable behavior is to set things up so that ANALYZE wi= ll accumulate stats about the values of "to_tsvector('simple',a.ts_vec= tor_fields)".  You can arrange that either by extracting that int= o a column, or by making an index on it.

 

     &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;     regards, tom lane


=D0is elektronisk=E2 pasta s=FBt=EEjums un taj=E2 eso=F0ie dokumenti ir par= edz=E7ts tikai nor=E2d=EEtajam adres=E2tam(-iem). Tas satur konfidenci=E2lu= inform=E2ciju un var satur=E7t ar=EE privili=EC=E7tu inform=E2ciju. T=E2 s= atura piln=EEga vai da=EF=E7ja nesankcion=E7ta izpau=F0ana, izmanto=F0ana v= ai t=E2l=E2ka izplat=EE=F0ana jebk=E2d=E2 veid=E2 ir aizliegta. Ja =F0is elektronisk=E2 = pasta s=FBt=EEjums sa=F2emts k=EF=FBdas d=E7=EF, l=FBdzam sazin=E2ties ar s= =FBt=EEt=E2ju, nos=FBtot atbildes e-pasta v=E7stuli, un izdz=E7st =F0o v=E7= stuli. Dro=F0=EEbas apsv=E7rumu d=E7=EF SIA Datakom var veikt elektronisk= =E2 pasta s=FBt=EEjumu un tiem pievienoto dokumentu satura monitoringu. Ja vien =F0aj=E2 v=E7stul= =E7 vai pievienotajos dokumentus nav nep=E2rprotami teikts pret=E7jais, =F0= is elektronisk=E2 pasta s=FBt=EEjums vai tam pievienotie dokumenti nav uzsk= at=E2mi par SIA Datakom pied=E2v=E2jumu sl=E7gt dar=EEjumu, ap=F2em=F0anos vai gatav=EEbu uz=F2emties saist=EEbas. S=FBt=EEt=E2js: SIA Datakom, vieno= tais re=ECistr=E2cijas nr: 40103142605, Juridisk=E2 adrese: Vien=EEbas gatv= e 109, R=EEga, LV-1058, Latvija

This electronic transmission and attached documents is intended only for th= e addressee(s). It contains confidential information and may also contain p= rivileged information. If you are not an intended recipient, you may not us= e, read, retransmit, disseminate or take any action in reliance upon it. If this electronic transmission is= received in error, please contact the sender by sending a reply e-mail and= delete this message. SIA Datakom reserves the right to monitor all e-mail = communications through its networks. Unless this letter or the accompanying documents clearly stated otherwise,= by this electronic transmission does not constitute a contract offer, a co= ntract amendment or an acceptance of a contract offer on behalf of the SIA = Datakom. Sender: SIA Datakom, registration number: 40103142605, Latvia. Registered office: Vienibas gatve 109, Riga, = LV-1058, Latvia

--_000_c081b34fc8584e4e80ddf0e99eabbba5datakomlv_--