Re: PostgreSQL 10.0 SELECT LIMIT performance problem

2018-09-12 10:31 GMT+02:00 Mareks Kalnačs <Mareks(dot)Kalnacs(at)datakom(dot)lv>:

> Hi!
>
>
>
> We have some serious performance problem with SELECTS when add limit, for
> example, execute time without limit ~250msec (316 rows returned), when add
> limit 20, execute time 15 – 50secs.
>
>
>
> We have select with subselect filter:
>
> select a.id, a.jdata
>
> from oss_alarms a
>
> where
>
> a.jdata->>'dn' in
>
> (
>
> select o.jdata->>'ossDn'
>
> from oss_objects o, tvc_entity e
>
> where e.jtype='object'
>
> and
> o.jdata->>'sid'=e.jdata->>'siteId'
>
> and
> tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%zaube%')
>
> )
>
> order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) desc
>
> limit 20;
>
>
>
> Select used to get data for user interface table view window. Table view
> has several filters what users can apply and subselect realize one of them.
> Subselect execution time always fine, but main select have some serous
> performance problems. When subselect replaced with static values, select
> execute time is fine.
>
> Table oss_alarms very often updated but only new records, there is about
> 10`000 to 30`000 new records per day and, when they processed, there are no
> more changes.
>
>
>
> Version string PostgreSQL 10.0 on powerpc64le-unknown-linux-gnu,
> compiled by gcc (GCC) 6.3.1 20170515 (Advance-Toolchain-at10.0) IBM AT 10
> branch, based on subversion id 248065., 64-bit
>
>
>
> I’m gathered some technical information about DB structure and execution
> (see attachment).
>

Sometimes LIMIT clause can confuse optimizator, when data are not uniform.

You can try OFFSET 0 trick:

Original query: SELECT * FROM t ORDER BY c LIMIT 10

transform to:

SELECT * FROM (original query without limit OFFSET 0) x LIMIT 10;

>
>
>
>
>
> *Mareks Kalnačs*
>
> Software Engineer
>
> Software Development Department
>
> T: +371 67628888
>
> M: +371 26479242
> @: Mareks(dot)Kalnacs(at)datakom(dot)lv
>
> *www.datakom.lv <http://www.datakom.lv>*, SIA Datakom, Vienības gatve
> 109, Rīga, Latvija, LV-1058
>
>
>
> ------------------------------
>
> Š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
>
> ------------------------------
>