RE: PostgreSQL 10.0 SELECT LIMIT performance problem

2018-09-12 10:31 GMT+02:00 Mareks Kalnačs <Mareks.Kalnacs@datakom.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.Kalnacs@datakom.lv

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

---