From: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
To: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Cc: Mâris Rucis <Maris.Rucis@datakom.lv>
Subject: PostgreSQL 10.0 SELECT LIMIT performance problem
Date: Wed, 12 Sep 2018 08:31:50 +0000
Message-ID: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv> (raw)
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
Im gathered some technical information about DB structure and execution (see attachment).
Mareks Kalnaès
Software Engineer
Software Development Department
T: +371 67628888
M: +371 26479242
@: Mareks.Kalnacs@datakom.lv<mailto:Mareks.Kalnacs@datakom.lv>
www.datakom.lv<http://www.datakom.lv;, SIA Datakom, Vienîbas gatve 109, Rîga, Latvija, LV-1058
[cid:image003.png@01D44A8B.E3896950]
________________________________
Ð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
________________________________
Attachments:image003.png (image/png, 15.8K) downloadview imagetechnical-data.txt (text/plain, 12.2K) download
--*****************************************************************************
--SELECT
--*****************************************************************************
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
--*****************************************************************************
--Explain without limit
--*****************************************************************************
Sort (cost=908238.42..910906.70 rows=1067313 width=809) (actual time=12.687..12.708 rows=316 loops=1)
Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
Sort Key: (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))) DESC
Sort Method: quicksort Memory: 343kB
Buffers: shared hit=946
-> Nested Loop (cost=2942.18..341714.36 rows=1067313 width=809) (actual time=10.046..12.465 rows=316 loops=1)
Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
Buffers: shared hit=946
-> HashAggregate (cost=2941.62..2946.37 rows=475 width=951) (actual time=9.977..9.993 rows=25 loops=1)
Output: o.jdata
Group Key: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=496
-> Nested Loop (cost=3.14..2940.43 rows=475 width=951) (actual time=6.865..9.954 rows=25 loops=1)
Output: o.jdata, (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=496
-> Index Scan using idx_entity_jtype on tvcis.tvc_entity e (cost=0.42..2396.65 rows=3 width=195) (actual time=6.827..9.875 rows=1 loops=1)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Index Cond: ((e.jtype)::text = 'object'::text)
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
Rows Removed by Filter: 2477
Buffers: shared hit=475
-> Bitmap Heap Scan on tvcis.oss_objects o (cost=2.71..179.68 rows=158 width=951) (actual time=0.025..0.053 rows=25 loops=1)
Output: o.id, o.jdata
Recheck Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Heap Blocks: exact=19
Buffers: shared hit=21
-> Bitmap Index Scan on idx_oss_objects_sid (cost=0.00..2.67 rows=158 width=0) (actual time=0.015..0.015 rows=25 loops=1)
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=2
-> Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a (cost=0.56..143.72 rows=211 width=801) (actual time=0.018..0.033 rows=13 loops=25)
Output: a.id, a.jdata, a.ts_vector, a.ts_vector_fields
Index Cond: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
Buffers: shared hit=410
Planning time: 0.881 ms
Execution time: 12.830 ms
--*****************************************************************************
--Explain with limit
--*****************************************************************************
Limit (cost=3.57..414.17 rows=20 width=809) (actual time=2536.405..19044.847 rows=20 loops=1)
Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
Buffers: shared hit=4507507 read=1614
-> Nested Loop Semi Join (cost=3.57..21912001.67 rows=1067313 width=809) (actual time=2536.404..19044.836 rows=20 loops=1)
Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
Join Filter: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
Rows Removed by Join Filter: 10717797
Buffers: shared hit=4507507 read=1614
-> Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a (cost=0.43..1360616.52 rows=2134626 width=801) (actual time=0.021..577.152 rows=428721 loops=1)
Output: a.id, a.jdata, a.ts_vector, a.ts_vector_fields
Buffers: shared hit=317389 read=1614
-> Materialize (cost=3.14..2942.81 rows=475 width=951) (actual time=0.000..0.002 rows=25 loops=428721)
Output: o.jdata
Buffers: shared hit=496
-> Nested Loop (cost=3.14..2940.43 rows=475 width=951) (actual time=7.477..11.007 rows=25 loops=1)
Output: o.jdata
Buffers: shared hit=496
-> Index Scan using idx_entity_jtype on tvcis.tvc_entity e (cost=0.42..2396.65 rows=3 width=195) (actual time=7.453..10.943 rows=1 loops=1)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Index Cond: ((e.jtype)::text = 'object'::text)
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
Rows Removed by Filter: 2477
Buffers: shared hit=475
-> Bitmap Heap Scan on tvcis.oss_objects o (cost=2.71..179.68 rows=158 width=951) (actual time=0.021..0.055 rows=25 loops=1)
Output: o.id, o.jdata
Recheck Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Heap Blocks: exact=19
Buffers: shared hit=21
-> Bitmap Index Scan on idx_oss_objects_sid (cost=0.00..2.67 rows=158 width=0) (actual time=0.012..0.012 rows=25 loops=1)
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=2
Planning time: 0.630 ms
Execution time: 19044.909 ms
--*****************************************************************************
--Data information
--*****************************************************************************
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%')
Rows: 25
Exec time: ~50msec
--*****************************************************************************
--data distribution
--*****************************************************************************
select count(*),
count(distinct a.jdata->>'dn'),
count(distinct (tvc_convert_array_to_date(a.jdata -> 'alarmTime')))
from oss_alarms a;
=> 2128873;59684;1136120
--*****************************************************************************
-- Table: oss_alarms
--*****************************************************************************
-- DROP TABLE oss_alarms;
CREATE TABLE oss_alarms
(
id bigint NOT NULL,
jdata jsonb,
ts_vector character varying,
ts_vector_fields character varying,
CONSTRAINT oss_alarms_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE oss_alarms
OWNER TO tvcis;
-- Index: idx_alarms_consecnbr
-- DROP INDEX idx_alarms_consecnbr;
CREATE INDEX idx_alarms_consecnbr
ON oss_alarms
USING btree
((jdata ->> 'consecNbr'::text) COLLATE pg_catalog."default");
-- Index: idx_oss_alarms_alarm_time
-- DROP INDEX idx_oss_alarms_alarm_time;
CREATE INDEX idx_oss_alarms_alarm_time
ON oss_alarms
USING btree
(tvc_convert_array_to_date(jdata -> 'alarmTime'::text) DESC);
-- Index: idx_oss_alarms_dn
-- DROP INDEX idx_oss_alarms_dn;
CREATE INDEX idx_oss_alarms_dn
ON oss_alarms
USING btree
((jdata ->> 'dn'::text) COLLATE pg_catalog."default");
-- Index: idx_oss_alarms_time_range
-- DROP INDEX idx_oss_alarms_time_range;
CREATE INDEX idx_oss_alarms_time_range
ON oss_alarms
USING gist
(tsrange(tvc_convert_array_to_date(jdata -> 'alarmTime'::text), tvc_convert_array_to_date(jdata -> 'cancelTime'::text), '[]'::text));
-- Index: idx_oss_alarms_update_timestamp
-- DROP INDEX idx_oss_alarms_update_timestamp;
CREATE INDEX idx_oss_alarms_update_timestamp
ON oss_alarms
USING btree
(tvc_convert_array_to_date(jdata -> 'updateTimestamp'::text) DESC);
-- Index: oss_alarms_textsearch_fields_idx
-- DROP INDEX oss_alarms_textsearch_fields_idx;
CREATE INDEX oss_alarms_textsearch_fields_idx
ON oss_alarms
USING gin
(to_tsvector('simple'::regconfig, ts_vector_fields::text));
-- Index: oss_alarms_textsearch_idx
-- DROP INDEX oss_alarms_textsearch_idx;
CREATE INDEX oss_alarms_textsearch_idx
ON oss_alarms
USING gin
(to_tsvector('simple'::regconfig, ts_vector::text));
-- Trigger: oss_alarms_briu on oss_alarms
-- DROP TRIGGER oss_alarms_briu ON oss_alarms;
CREATE TRIGGER oss_alarms_briu
BEFORE INSERT OR UPDATE
ON oss_alarms
FOR EACH ROW
EXECUTE PROCEDURE oss_alarms_briu();
-- Function: oss_alarms_briu()
-- DROP FUNCTION oss_alarms_briu();
CREATE OR REPLACE FUNCTION oss_alarms_briu()
RETURNS trigger AS
$BODY$ begin
NEW.ts_vector := jsonb_values_as_string(NEW.jdata); -- reurns json values without keys
NEW.ts_vector_fields := jsonb_values_as_string_with_fieldnames(NEW.jdata); -- returns json key/value pairs divided by words
return new;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION oss_alarms_briu()
OWNER TO tvcis;
--*****************************************************************************
-- oss_alarms.jdata structure (in java)
--*****************************************************************************
public class OssAlarmJson extends BaseJson {
@Column(unique = true, nullable = false, length = 22, name = "CONSEC_NBR")
private Long consecNbr;
@Column(length = 22, name = "NE_GID")
private Long neGid;
@Column(nullable = false, length = 22, name = "ALARM_NUMBER")
private Long alarmNumber;
@Column(nullable = false, length = 7, name = "ALARM_TIME")
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
private LocalDateTime alarmTime;
@Column(length = 7, name = "CANCEL_TIME")
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
private LocalDateTime cancelTime;
@Column(length = 7, name = "ACK_TIME")
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
private LocalDateTime ackTime;
@Column(length = 22, name = "ACK_STATUS")
private Long ackStatus;
@Column(length = 65, name = "ACKED_BY")
private String ackedBy;
@Column(length = 1025, name = "TEXT")
private String text;
@Column(length = 401, name = "DN")
private String dn;
@Column(length = 1025, name = "USER_ADDITIONAL_INFO")
private String userAdditionalInfo;
@Column(length = 1025, name = "SUPPLEMENTARY_INFO")
private String supplementaryInfo;
@Column(nullable = false, length = 22, name = "ALARM_STATUS")
private Long alarmStatus;
@Column(nullable = false, length = 22, name = "SEVERITY")
private Long severity;
@Column(length = 65, name = "CANCELLED_BY")
private String cancelledBy;
@Column(length = 11, name = "UPDATE_TIMESTAMP")
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@XmlJavaTypeAdapter(value = LocalDateTimeAdapter.class)
private LocalDateTime updateTimestamp;
@Column(length = 1025, name = "DIAGNOSTIC_INFO")
private String diagnosticInfo;
@Column(length = 50, name = "DECODED_TYPE")
private List<String> decodedType;
@Column(length = 50, name = "TECH")
private String tech;
private Long commentId;
private String dnSplit;
}
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, Maris.Rucis@datakom.lv
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <7419b7a3a21d4f1c8509178a071ec636@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