From: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Victor Yegorov <vyegorov@gmail.com>
Cc: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Cc: Mâris Rucis <Maris.Rucis@datakom.lv>
Cc: Pâvels Korþs <Pavels.Korzs@datakom.lv>
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
Date: Thu, 13 Sep 2018 10:19:04 +0000
Message-ID: <c081b34fc8584e4e80ddf0e99eabbba5@datakom.lv> (raw)
In-Reply-To: <9924.1536771851@sss.pgh.pa.us>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
<CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
<e65c26cc8fae43abbad69b3e51d1579d@datakom.lv>
<CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
<9924.1536771851@sss.pgh.pa.us>
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=0.28..1856.72 rows=253 width=208) (actual time=0.091..13.944 rows=71 loops=1)
Table2: Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..16.84 rows=23 width=951) (actual time=0.009..0.033 rows=18 loops=71)
Join: Nested Loop (cost=0.57..6175.55 rows=9 width=951) (actual time=0.112..17.355 rows=1291 loops=1)
Why planned dont take in account results from each table:
Join rows = Table1 rows * Table2 rows == 253 * 23 = 5819? In this case 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 * 18 ~= 1291
Mareks
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: treðdiena, 2018. gada 12. septembris 20:04
To: Victor Yegorov <vyegorov@gmail.com>
Cc: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>; pgsql-bugs@postgresql.org; Mâris Rucis <Maris.Rucis@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
Victor Yegorov <vyegorov@gmail.com<mailto: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 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 possibility is to make expression indexes on those important keys.
ANALYZE will gather stats on the values of indexed expressions, and then perhaps the planner will have enough info to make better decisions.
In the other case mentioned, where the problem is a poor guess about the selectivity of
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsGenerator) '
)
)
it's the same problem: the planner 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 complaining that planning time was too long. The only way to get reasonable behavior is to set things up so that ANALYZE will accumulate 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 making an index on it.
regards, tom lane
________________________________
Ðis elektroniskâ pasta sûtîjums un tajâ esoðie dokumenti ir paredzçts tikai norâdîtajam adresâtam(-iem). Tas satur konfidenciâlu informâciju un var saturçt arî priviliìçtu informâciju. Tâ satura pilnîga vai daïçja nesankcionçta izpauðana, izmantoðana vai tâlâka izplatîðana jebkâdâ veidâ ir aizliegta. Ja ðis elektroniskâ pasta sûtîjums saòemts kïûdas dçï, lûdzam sazinâties ar sûtîtâju, nosûtot atbildes e-pasta vçstuli, un izdzçst ðo vçstuli. Droðîbas apsvçrumu dçï SIA Datakom var veikt elektroniskâ pasta sûtîjumu un tiem pievienoto dokumentu satura monitoringu. Ja vien ðajâ vçstulç vai pievienotajos dokumentus nav nepârprotami teikts pretçjais, ðis elektroniskâ pasta sûtîjums vai tam pievienotie dokumenti nav uzskatâmi par SIA Datakom piedâvâjumu slçgt darîjumu, apòemðanos vai gatavîbu uzòemties saistîbas. Sûtîtâjs: SIA Datakom, vienotais reìistrâcijas nr: 40103142605, Juridiskâ adrese: Vienîbas gatve 109, Rîga, LV-1058, Latvija
This electronic transmission and attached documents is intended only for the addressee(s). It contains confidential information and may also contain privileged information. If you are not an intended recipient, you may not use, 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 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
________________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Mareks.Kalnacs@datakom.lv, tgl@sss.pgh.pa.us, vyegorov@gmail.com, Maris.Rucis@datakom.lv, Pavels.Korzs@datakom.lv
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <c081b34fc8584e4e80ddf0e99eabbba5@datakom.lv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox