pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 10.0 SELECT LIMIT performance problem
13+ messages / 7 participants
[nested] [flat]
* PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 08:31 Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
0 siblings, 2 replies; 13+ messages in thread
From: Mareks Kalnaès @ 2018-09-12 08:31 UTC (permalink / raw)
To: pgsql-bugs; +Cc: Mâris Rucis <Maris.Rucis@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
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) download
view image
technical-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;
}
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 12:31 Victor Yegorov <vyegorov@gmail.com>
parent: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
1 sibling, 1 reply; 13+ messages in thread
From: Victor Yegorov @ 2018-09-12 12:31 UTC (permalink / raw)
To: Mareks.Kalnacs@datakom.lv; +Cc: pgsql-bugs; Maris.Rucis@datakom.lv
ср, 12 сент. 2018 г. в 11:39, Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>:
> 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.
>
Hi, Mareks.
As this is not actually a bug, it's better to use pgsql-performance or
pgsql-general for such questions next time.
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;
>
As execution plan without limit shows, your join conditions yield 316 rows,
explicit sort is fast for this amount
For the plan with the limit, planner prefers to use
`idx_oss_alarms_alarm_time` index, to avoid extra sort.
This leads to:
Rows Removed by Join Filter: 10717797
I.e. you're reading 10M rows via index scan and later throw them away, as
they do not match your join condition:
Join Filter: ((a.jdata ->> 'dn'::text) = (o.jdata ->>
'ossDn'::text))
It looks like a.jdata->'dn' and a.jdata->'alarmTime' are correlated,
although planner doesn't knows that.
PostgreSQL 10 has `CREATE STATISTICS`, but it works on table columns, not
expressions.
You can try disabling `idx_oss_alarms_alarm_time` by using expression in
`ORDER BY`:
order by (idx_oss_alarms_alarm_timetvc_convert_array_to_date(a.jdata ->
'alarmTime'))+INTERVAL '0' desc
But be warned — this can help in some situations and make things worse in
others.
I would recommend to move JOIN and ORDER BY columns out of JSON and make
them direct table columns.
--
Victor Yegorov
Attachments:
image003.png (image/png, 15.8K) download
view image
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 12:43 Pavel Stehule <pavel.stehule@gmail.com>
parent: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
1 sibling, 1 reply; 13+ messages in thread
From: Pavel Stehule @ 2018-09-12 12:43 UTC (permalink / raw)
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>; +Cc: pgsql-bugs; Māris Rucis <Maris.Rucis@datakom.lv>
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 <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
>
> ------------------------------
>
Attachments:
image003.png (image/png, 15.8K) download
view image
* RE: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 13:16 Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Mareks Kalnačs @ 2018-09-12 13:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-bugs; Māris Rucis <Maris.Rucis@datakom.lv>
Hi Pavel!
He know how to trick SQL, but this not a solution. We have different filters with different distribution fields, for example, jdata->>'dn' distribution is 2.8%, but jdata->>’tech’ 25%, this means we must get count and based on count modify select. This is DB server job, not an application server job.
Best regards,
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:image002.png@01D44AB3.AED25B90]
From: Pavel Stehule <pavel.stehule@gmail.com>
Sent: trešdiena, 2018. gada 12. septembris 15:43
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
Cc: pgsql-bugs@postgresql.org; Māris Rucis <Maris.Rucis@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
2018-09-12 10:31 GMT+02:00 Mareks Kalnačs <Mareks.Kalnacs@datakom.lv<mailto: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<http://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<mailto:Mareks.Kalnacs@datakom.lv>
www.datakom.lv<http://www.datakom.lv;, SIA Datakom, Vienības gatve 109, Rīga, Latvija, LV-1058
[cid:image001.png@01D44AB1.2B8636F0]
________________________________
Š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
________________________________
________________________________
Š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:
image001.png (image/png, 15.8K) download
view image
image002.png (image/png, 15.8K) download
view image
* RE: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 16:23 Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
parent: Victor Yegorov <vyegorov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mareks Kalnačs @ 2018-09-12 16:23 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-bugs; Māris Rucis <Maris.Rucis@datakom.lv>
Hi Victor!
I will disagree with you about bug. I understand that foreign keys will solve some issues and we already making changes add FK, but this only bypass problem, not solve it and in different conditions we got problem again. Main issue, optimizer not take in account index distribution. In current case main cause actually is subselect. I little bit played around with indexes and different select conditions and got very strange behavior. What we done, add new index for tvc_entity table (idx_tvc_entity_sid_u) and run full analyze for all tables and got problem in reverse, now optimizer thinks that he will get less rows from subselect.
Subselect:
select o.jdata->>'ossDn'
from oss_objects o, tvc_entity e
where e.jtype='object'
and e.jdata->>'siteId' IS NOT NULL
and o.jdata->>'sid'=e.jdata->>'siteId'
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%za%')
oss_objects.jdata->>’sid’ distribution is 4.2% , null value count 319, total rows 31693, but execution plan always think that real rows returned will be less than actual:
Gather (cost=1014.16..5280.19 rows=9 width=32) (actual time=1.304..18.982 rows=1291 loops=1)
-> Nested Loop (cost=14.16..4279.28 rows=5 width=32) (actual time=3.172..14.497 rows=646 loops=2)
-> Parallel Bitmap Heap Scan on tvcis.tvc_entity e (cost=13.87..1694.08 rows=149 width=208) (actual time=3.017..11.597 rows=36 loops=2)
-> Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..17.12 rows=23 width=951) (actual time=0.015..0.054 rows=18 loops=71)
If oss_objects.jdata->>’sid’ distribution is 4.2% then for every unique tvc_entity.jdata->>’siteId’ will be selected 31693 * 4.2% = 23 rows, this means join must return more rows than select from tvc_entity, what is real result, but optimized think in reverse.
We have another example, where we don’t understand how to solve it, and this may not a bug. In this example we have full text search index within one table, and, when we hit value with low hit count, select runs slow:
select a.id, a.jdata
from tvcis.oss_alarms a
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsGenerator) '
)
)
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) DESC
limit 20;
Bad case:
Sort (cost=8787.37..8799.58 rows=4885 width=809) (actual time=7.351..7.423 rows=586 loops=1)
Without limit:
Planning time: 0.301 ms
Execution time: 7.574 ms
With limit:
Planning time: 0.312 ms
Execution time: 4972.245 ms
Good case:
From count:
-> Parallel Bitmap Heap Scan on tvcis.oss_alarms a (cost=5080.23..455246.09 rows=248615 width=0) (actual time=1142.280..175706.097 rows=199195 loops=3)
Without limit:
Too many rows to test, get count instead
With limit:
Planning time: 0.370 ms
Execution time: 43.131 ms
But counts is a nightmare:
Planning time: 0.236 ms
Execution time: 176343.730 ms
Couple examples of explain plan attached.
Best regards,
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:image002.png@01D44ACD.C28FEBB0]
From: Victor Yegorov <vyegorov@gmail.com>
Sent: trešdiena, 2018. gada 12. septembris 15:32
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
Cc: pgsql-bugs@postgresql.org; Māris Rucis <Maris.Rucis@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
ср, 12 сент. 2018 г. в 11:39, Mareks Kalnačs <Mareks.Kalnacs@datakom.lv<mailto:Mareks.Kalnacs@datakom.lv>>:
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.
Hi, Mareks.
As this is not actually a bug, it's better to use pgsql-performance or pgsql-general for such questions next time.
We have select with subselect filter:
select a.id<http://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;
As execution plan without limit shows, your join conditions yield 316 rows, explicit sort is fast for this amount
For the plan with the limit, planner prefers to use `idx_oss_alarms_alarm_time` index, to avoid extra sort.
This leads to:
Rows Removed by Join Filter: 10717797
I.e. you're reading 10M rows via index scan and later throw them away, as they do not match your join condition:
Join Filter: ((a.jdata ->> 'dn'::text) = (o.jdata ->> 'ossDn'::text))
It looks like a.jdata->'dn' and a.jdata->'alarmTime' are correlated, although planner doesn't knows that.
PostgreSQL 10 has `CREATE STATISTICS`, but it works on table columns, not expressions.
You can try disabling `idx_oss_alarms_alarm_time` by using expression in `ORDER BY`:
order by (idx_oss_alarms_alarm_timetvc_convert_array_to_date(a.jdata -> 'alarmTime'))+INTERVAL '0' desc
But be warned — this can help in some situations and make things worse in others.
I would recommend to move JOIN and ORDER BY columns out of JSON and make them direct table columns.
--
Victor Yegorov
________________________________
Š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:
image002.png (image/png, 15.8K) download
view image
technical-data-2.txt (text/plain, 25.3K) download
-- ****************************************************************
select o.jdata->>'ossDn'
from oss_objects o, tvc_entity e
where e.jtype='object'
and e.jdata->>'siteId' IS NOT NULL
and o.jdata->>'sid'=e.jdata->>'siteId'
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%zaube%')
-- ****************************************************************
Nested Loop (cost=14.09..1894.09 rows=1 width=32) (actual time=6.473..10.349 rows=25 loops=1)
Output: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=453
-> Bitmap Heap Scan on tvcis.tvc_entity e (cost=13.80..1834.65 rows=2 width=208) (actual time=6.451..10.279 rows=1 loops=1)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
Rows Removed by Filter: 1943
Heap Blocks: exact=425
Buffers: shared hit=432
-> Bitmap Index Scan on idx_tvc_entity_sid_u (cost=0.00..13.80 rows=1265 width=0) (actual time=0.155..0.155 rows=1944 loops=1)
Buffers: shared hit=7
-> Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..29.49 rows=23 width=951) (actual time=0.017..0.050 rows=25 loops=1)
Output: o.id, o.jdata
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=21
Planning time: 0.502 ms
Execution time: 10.397 ms
-- ****************************************************************
select o.jdata->>'ossDn'
from oss_objects o, tvc_entity e
where e.jtype='object'
and e.jdata->>'siteId' IS NOT NULL
and o.jdata->>'sid'=e.jdata->>'siteId'
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%za%')
-- ****************************************************************
Gather (cost=1014.16..5280.19 rows=9 width=32) (actual time=1.247..20.071 rows=1291 loops=1)
Output: ((o.jdata ->> 'ossDn'::text))
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1185
-> Nested Loop (cost=14.16..4279.28 rows=5 width=32) (actual time=3.392..15.343 rows=646 loops=2)
Output: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=1548
Worker 0: actual time=5.903..11.889 rows=187 loops=1
Buffers: shared hit=363
-> Parallel Bitmap Heap Scan on tvcis.tvc_entity e (cost=13.87..1694.08 rows=149 width=208) (actual time=3.234..12.305 rows=36 loops=2)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%za%'::text)
Rows Removed by Filter: 936
Heap Blocks: exact=330
Buffers: shared hit=544
Worker 0: actual time=5.634..10.450 rows=13 loops=1
Buffers: shared hit=207
-> Bitmap Index Scan on idx_tvc_entity_sid_u (cost=0.00..13.80 rows=1265 width=0) (actual time=0.387..0.387 rows=1944 loops=1)
Buffers: shared hit=7
-> Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..17.12 rows=23 width=951) (actual time=0.016..0.057 rows=18 loops=71)
Output: o.id, o.jdata
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=1004
Worker 0: actual time=0.023..0.087 rows=14 loops=13
Buffers: shared hit=156
Planning time: 0.542 ms
Execution time: 23.079 ms
-- ****************************************************************
select o.jdata->>'ossDn'
from oss_objects o, tvc_entity e
where e.jtype='object'
and e.jdata->>'siteId' IS NOT NULL
and o.jdata->>'sid'=e.jdata->>'siteId'
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%z%')
-- ****************************************************************
Gather (cost=1014.41..6281.48 rows=44 width=32) (actual time=1.166..29.045 rows=5722 loops=1)
Output: ((o.jdata ->> 'ossDn'::text))
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=3333 read=30
-> Nested Loop (cost=14.41..5277.03 rows=26 width=32) (actual time=2.817..23.225 rows=2861 loops=2)
Output: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=5150 read=39
Worker 0: actual time=4.802..21.169 rows=1943 loops=1
Buffers: shared hit=1817 read=9
-> Parallel Bitmap Heap Scan on tvcis.tvc_entity e (cost=14.12..1694.33 rows=744 width=208) (actual time=2.702..10.859 rows=176 loops=2)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%z%'::text)
Rows Removed by Filter: 796
Heap Blocks: exact=292
Buffers: shared hit=544
Worker 0: actual time=4.754..10.699 rows=118 loops=1
Buffers: shared hit=245
-> Bitmap Index Scan on idx_tvc_entity_sid_u (cost=0.00..13.80 rows=1265 width=0) (actual time=0.329..0.329 rows=1944 loops=1)
Buffers: shared hit=7
-> Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..4.59 rows=23 width=951) (actual time=0.013..0.048 rows=16 loops=353)
Output: o.id, o.jdata
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=4606 read=39
Worker 0: actual time=0.015..0.066 rows=16 loops=118
Buffers: shared hit=1572 read=9
Planning time: 0.530 ms
Execution time: 33.413 ms
-- ****************************************************************
-- full select after add one index and run full analyse on all tables
-- ****************************************************************
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 e.jdata->>'siteId' IS NOT NULL
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
offset 0 limit 20;
-- ****************************************************************
Limit (cost=2057.69..2057.74 rows=20 width=809) (actual time=9.401..9.404 rows=20 loops=1)
Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
Buffers: shared hit=903
-> Sort (cost=2057.69..2058.22 rows=211 width=809) (actual time=9.400..9.401 rows=20 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: top-N heapsort Memory: 58kB
Buffers: shared hit=903
-> Nested Loop (cost=1894.65..2052.08 rows=211 width=809) (actual time=6.996..9.261 rows=316 loops=1)
Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
Buffers: shared hit=903
-> HashAggregate (cost=1894.09..1894.10 rows=1 width=951) (actual time=6.949..6.955 rows=25 loops=1)
Output: o.jdata
Group Key: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=453
-> Nested Loop (cost=14.09..1894.09 rows=1 width=951) (actual time=4.406..6.930 rows=25 loops=1)
Output: o.jdata, (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=453
-> Bitmap Heap Scan on tvcis.tvc_entity e (cost=13.80..1834.65 rows=2 width=208) (actual time=4.393..6.878 rows=1 loops=1)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Recheck Cond: (((e.jtype)::text = 'object'::text) AND ((e.jdata ->> 'siteId'::text) IS NOT NULL))
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%zaube%'::text)
Rows Removed by Filter: 1943
Heap Blocks: exact=425
Buffers: shared hit=432
-> Bitmap Index Scan on idx_tvc_entity_sid_u (cost=0.00..13.80 rows=1265 width=0) (actual time=0.102..0.102 rows=1944 loops=1)
Buffers: shared hit=7
-> Index Scan using idx_oss_objects_sid on tvcis.oss_objects o (cost=0.29..29.49 rows=23 width=951) (actual time=0.010..0.038 rows=25 loops=1)
Output: o.id, o.jdata
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=21
-> Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a (cost=0.56..102.59 rows=211 width=801) (actual time=0.016..0.035 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.992 ms
Execution time: 9.462 ms
-- ****************************************************************
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 e.jdata->>'siteId' IS NOT NULL
and o.jdata->>'sid'=e.jdata->>'siteId'
and tvc_unaccent(e.jdata->>'name') like tvc_unaccent('%za%')
)
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) desc
offset 0 limit 20;
-- ****************************************************************
Limit (cost=7649.11..7649.16 rows=20 width=809) (actual time=81409.102..81409.108 rows=20 loops=1)
Output: a.id, a.jdata, (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text)))
Buffers: shared hit=40048 read=7044 dirtied=9
-> Sort (cost=7649.11..7653.87 rows=1903 width=809) (actual time=81409.101..81409.104 rows=20 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: top-N heapsort Memory: 58kB
Buffers: shared hit=40048 read=7044 dirtied=9
-> Nested Loop (cost=6176.13..7598.47 rows=1903 width=809) (actual time=18.561..81384.112 rows=37312 loops=1)
Output: a.id, a.jdata, tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))
Buffers: shared hit=40048 read=7044 dirtied=9
-> HashAggregate (cost=6175.57..6175.66 rows=9 width=951) (actual time=18.491..19.704 rows=1291 loops=1)
Output: o.jdata
Group Key: (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=2705
-> Nested Loop (cost=0.57..6175.55 rows=9 width=951) (actual time=0.112..17.355 rows=1291 loops=1)
Output: o.jdata, (o.jdata ->> 'ossDn'::text)
Buffers: shared hit=2705
-> 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)
Output: e.id, e.jtype, e.jdata, e.ts_vector, e.jmetadata, e.ts_vector_fields
Filter: (tvc_unaccent((e.jdata ->> 'name'::text)) ~~ '%za%'::text)
Rows Removed by Filter: 1873
Buffers: shared hit=1702
-> 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)
Output: o.id, o.jdata
Index Cond: ((o.jdata ->> 'sid'::text) = (e.jdata ->> 'siteId'::text))
Buffers: shared hit=1003
-> Index Scan using idx_oss_alarms_dn on tvcis.oss_alarms a (cost=0.56..102.59 rows=211 width=801) (actual time=2.320..62.536 rows=29 loops=1291)
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=33869 read=7028 dirtied=9
Planning time: 1.593 ms
Execution time: 81409.196 ms
-- ****************************************************************
-- Table: oss_objects
-- ****************************************************************
-- DROP TABLE oss_objects;
CREATE TABLE oss_objects
(
id bigint NOT NULL,
jdata jsonb,
CONSTRAINT oss_objects_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE oss_objects
OWNER TO tvcis;
-- Index: idx_objects_gid
-- DROP INDEX idx_objects_gid;
CREATE INDEX idx_objects_gid
ON oss_objects
USING btree
((jdata ->> 'gid'::text) COLLATE pg_catalog."default");
-- Index: idx_objects_ossdn
-- DROP INDEX idx_objects_ossdn;
CREATE INDEX idx_objects_ossdn
ON oss_objects
USING btree
((jdata ->> 'ossDn'::text) COLLATE pg_catalog."default");
-- Index: idx_oss_objects_sid
-- DROP INDEX idx_oss_objects_sid;
CREATE INDEX idx_oss_objects_sid
ON oss_objects
USING btree
((jdata ->> 'sid'::text) COLLATE pg_catalog."default");
-- ****************************************************************
-- Table: tvc_entity
-- ****************************************************************
-- DROP TABLE tvc_entity;
CREATE TABLE tvc_entity
(
id bigint NOT NULL,
jtype character varying(64) NOT NULL,
jdata jsonb,
ts_vector character varying,
jmetadata jsonb,
ts_vector_fields character varying,
CONSTRAINT tvc_entity_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tvc_entity
OWNER TO tvcis;
-- Index: idx_entity_jdata
-- DROP INDEX idx_entity_jdata;
CREATE INDEX idx_entity_jdata
ON tvc_entity
USING gin
(jdata);
-- Index: idx_entity_jtype
-- DROP INDEX idx_entity_jtype;
CREATE INDEX idx_entity_jtype
ON tvc_entity
USING btree
(jtype COLLATE pg_catalog."default");
-- Index: idx_entity_meta_createdate
-- DROP INDEX idx_entity_meta_createdate;
CREATE INDEX idx_entity_meta_createdate
ON tvc_entity
USING btree
((jmetadata -> 'createDate'::text));
-- Index: idx_entity_meta_lasteditdate
-- DROP INDEX idx_entity_meta_lasteditdate;
CREATE INDEX idx_entity_meta_lasteditdate
ON tvc_entity
USING btree
((jmetadata -> 'lastEditDate'::text));
-- Index: idx_entity_name
-- DROP INDEX idx_entity_name;
CREATE INDEX idx_entity_name
ON tvc_entity
USING btree
((jdata ->> 'name'::text) COLLATE pg_catalog."default");
-- Index: idx_tvc_entity_enddate
-- DROP INDEX idx_tvc_entity_enddate;
CREATE INDEX idx_tvc_entity_enddate
ON tvc_entity
USING btree
(tvc_convert_array_to_date(jdata -> 'endDate'::text));
-- Index: idx_tvc_entity_planneddate
-- DROP INDEX idx_tvc_entity_planneddate;
CREATE INDEX idx_tvc_entity_planneddate
ON tvc_entity
USING btree
(tvc_convert_array_to_date(jdata -> 'plannedDate'::text));
-- Index: idx_tvc_entity_sid_u
-- DROP INDEX idx_tvc_entity_sid_u;
CREATE UNIQUE INDEX idx_tvc_entity_sid_u
ON tvc_entity
USING btree
((jdata ->> 'siteId'::text) COLLATE pg_catalog."default")
WHERE (jdata ->> 'siteId'::text) IS NOT NULL;
-- Index: idx_tvc_entity_startdate
-- DROP INDEX idx_tvc_entity_startdate;
CREATE INDEX idx_tvc_entity_startdate
ON tvc_entity
USING btree
(tvc_convert_array_to_date(jdata -> 'startDate'::text));
-- Index: idx_tvc_entity_startdate_enddate
-- DROP INDEX idx_tvc_entity_startdate_enddate;
CREATE INDEX idx_tvc_entity_startdate_enddate
ON tvc_entity
USING gist
(tsrange(tvc_convert_array_to_date(jdata -> 'startDate'::text), tvc_convert_array_to_date(jdata -> 'endDate'::text), '[]'::text));
-- Index: idx_tvc_entity_startdate_planneddate
-- DROP INDEX idx_tvc_entity_startdate_planneddate;
CREATE INDEX idx_tvc_entity_startdate_planneddate
ON tvc_entity
USING gist
(tsrange(tvc_convert_array_to_date(jdata -> 'startDate'::text), tvc_convert_array_to_date(jdata -> 'plannedDate'::text), '[]'::text));
-- Index: tvc_entity_textsearch_fields_idx
-- DROP INDEX tvc_entity_textsearch_fields_idx;
CREATE INDEX tvc_entity_textsearch_fields_idx
ON tvc_entity
USING gin
(to_tsvector('simple'::regconfig, ts_vector_fields::text));
-- Index: tvc_entity_textsearch_idx
-- DROP INDEX tvc_entity_textsearch_idx;
CREATE INDEX tvc_entity_textsearch_idx
ON tvc_entity
USING gin
(to_tsvector('simple'::regconfig, ts_vector::text));
-- Trigger: tvc_entity_audit on tvc_entity
-- DROP TRIGGER tvc_entity_audit ON tvc_entity;
CREATE TRIGGER tvc_entity_audit
BEFORE INSERT OR UPDATE OR DELETE
ON tvc_entity
FOR EACH ROW
EXECUTE PROCEDURE tvc_entity_audit();
-- Trigger: tvc_entity_briu on tvc_entity
-- DROP TRIGGER tvc_entity_briu ON tvc_entity;
CREATE TRIGGER tvc_entity_briu
BEFORE INSERT OR UPDATE
ON tvc_entity
FOR EACH ROW
EXECUTE PROCEDURE tvc_entity_briu();
-- ****************************************************************
-- Bed example
-- ****************************************************************
select a.id, a.jdata
from tvcis.oss_alarms a
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsGenerator) '
)
)
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) DESC
limit 20;
-- ****************************************************************
Without limit:
Sort (cost=8787.37..8799.58 rows=4885 width=809) (actual time=7.351..7.423 rows=586 loops=1)
Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
Sort Key: (tvc_convert_array_to_date((a.jdata -> 'alarmTime'::text))) DESC
Sort Method: quicksort Memory: 620kB
Buffers: shared hit=731
-> Bitmap Heap Scan on tvcis.oss_alarms a (cost=233.46..8488.06 rows=4885 width=809) (actual time=1.402..6.772 rows=586 loops=1)
Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
Heap Blocks: exact=570
Buffers: shared hit=731
-> Bitmap Index Scan on oss_alarms_textsearch_fields_idx (cost=0.00..232.24 rows=4885 width=0) (actual time=1.188..1.188 rows=587 loops=1)
Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
Buffers: shared hit=161
Planning time: 0.301 ms
Execution time: 7.574 ms
-- ****************************************************************
With limit:
Limit (cost=0.43..5769.24 rows=20 width=809) (actual time=785.691..4972.198 rows=20 loops=1)
Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
Buffers: shared hit=24207
-> Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a (cost=0.43..1409033.18 rows=4885 width=809) (actual time=785.689..4972.186 rows=20 loops=1)
Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
Filter: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
Rows Removed by Filter: 32588
Buffers: shared hit=24207
Planning time: 0.312 ms
Execution time: 4972.245 ms
-- ****************************************************************
-- Good example
-- ****************************************************************
select a.id, a.jdata
from tvcis.oss_alarms a
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsCore) '
)
)
order by (tvc_convert_array_to_date(a.jdata -> 'alarmTime')) DESC
limit 20;
-- ****************************************************************
Without limit:
Too many rows to test
-- ****************************************************************
With limit:
Limit (cost=0.43..52.67 rows=20 width=809) (actual time=0.358..43.088 rows=20 loops=1)
Output: id, jdata, (tvc_convert_array_to_date((jdata -> 'alarmTime'::text)))
Buffers: shared hit=93
-> Index Scan using idx_oss_alarms_alarm_time on tvcis.oss_alarms a (cost=0.43..1558460.15 rows=596675 width=809) (actual time=0.356..43.080 rows=20 loops=1)
Output: id, jdata, tvc_convert_array_to_date((jdata -> 'alarmTime'::text))
Filter: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
Rows Removed by Filter: 107
Buffers: shared hit=93
Planning time: 0.370 ms
Execution time: 43.131 ms
-- ****************************************************************
-- count with many hits
select count(*)
from tvcis.oss_alarms a
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsCore) '
)
)
-- ****************************************************************
Finalize Aggregate (cost=456867.84..456867.85 rows=1 width=8) (actual time=176328.408..176328.408 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=13201 read=89899
-> Gather (cost=456867.63..456867.84 rows=2 width=8) (actual time=176328.346..176328.391 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13201 read=89899
-> Partial Aggregate (cost=455867.63..455867.64 rows=1 width=8) (actual time=175785.805..175785.806 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=39539 read=267264
Worker 0: actual time=175514.664..175514.664 rows=1 loops=1
Buffers: shared hit=13449 read=88968
Worker 1: actual time=175514.661..175514.661 rows=1 loops=1
Buffers: shared hit=12889 read=88397
-> Parallel Bitmap Heap Scan on tvcis.oss_alarms a (cost=5080.23..455246.09 rows=248615 width=0) (actual time=1142.280..175706.097 rows=199195 loops=3)
Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
Rows Removed by Index Recheck: 110314
Heap Blocks: exact=31345 lossy=57106
Buffers: shared hit=39539 read=267264
Worker 0: actual time=926.364..175435.071 rows=198772 loops=1
Buffers: shared hit=13449 read=88968
Worker 1: actual time=926.401..175435.211 rows=197990 loops=1
Buffers: shared hit=12889 read=88397
-> Bitmap Index Scan on oss_alarms_textsearch_fields_idx (cost=0.00..4931.06 rows=596675 width=0) (actual time=1549.231..1549.231 rows=598899 loops=1)
Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmscore'''::tsquery)
Buffers: shared hit=1 read=274
Planning time: 0.236 ms
Execution time: 176343.730 ms
-- ****************************************************************
-- count with few hits
select count(*)
from tvcis.oss_alarms a
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsGenerator) '
)
)
-- ****************************************************************
Aggregate (cost=7266.81..7266.82 rows=1 width=8) (actual time=1.843..1.843 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=731
-> Bitmap Heap Scan on tvcis.oss_alarms a (cost=233.46..7254.60 rows=4885 width=0) (actual time=0.823..1.775 rows=586 loops=1)
Output: id, jdata, ts_vector, ts_vector_fields
Recheck Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
Heap Blocks: exact=570
Buffers: shared hit=731
-> Bitmap Index Scan on oss_alarms_textsearch_fields_idx (cost=0.00..232.24 rows=4885 width=0) (actual time=0.765..0.765 rows=587 loops=1)
Index Cond: (to_tsvector('simple'::regconfig, (a.ts_vector_fields)::text) @@ '''decodedtype5b6f0753ossalarmsgenerator'''::tsquery)
Buffers: shared hit=161
Planning time: 0.248 ms
Execution time: 1.883 ms
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 16:43 Victor Yegorov <vyegorov@gmail.com>
parent: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
0 siblings, 2 replies; 13+ messages in thread
From: Victor Yegorov @ 2018-09-12 16:43 UTC (permalink / raw)
To: Mareks.Kalnacs@datakom.lv; +Cc: pgsql-bugs; Maris.Rucis@datakom.lv
ср, 12 сент. 2018 г. в 19:21, Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>:
> I will disagree with you about bug. I understand that foreign keys will
> solve some issues and we already making changes add FK, but this only
> bypass problem, not solve it and in different conditions we got problem
> again. Main issue, optimizer not take in account index distribution. In
> current case main cause actually is subselect. I little bit played around
> with indexes and different select conditions and got very strange behavior.
> What we done, add new index for tvc_entity table (idx_tvc_entity_sid_u) and
> run full analyze for all tables and got problem in reverse, now optimizer
> thinks that he will get less rows from subselect.
>
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.
That is the reason I outlined, that important keys should be extracted into
plain columns.
If you still consider this is a bug, please — send isolated reproducible
test case that demonstrates the bug.
--
Victor Yegorov
Attachments:
image002.png (image/png, 15.8K) download
view image
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 17:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Victor Yegorov <vyegorov@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Tom Lane @ 2018-09-12 17:04 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; +Cc: Mareks.Kalnacs@datakom.lv; pgsql-bugs; Maris.Rucis@datakom.lv
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 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-12 17:14 Adrien Nayrat <adrien.nayrat@anayrat.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Adrien Nayrat @ 2018-09-12 17:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Victor Yegorov <vyegorov@gmail.com>; +Cc: Mareks.Kalnacs@datakom.lv; pgsql-bugs; Maris.Rucis@datakom.lv
On 9/12/18 7:04 PM, Tom Lane wrote:
> 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 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.
Hi,
For what it can help, I wrote this article explaining that:
https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-and-statistics/
Regards,
Attachments:
signature.asc (application/pgp-signature, 487B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-13 10:03 Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
parent: Victor Yegorov <vyegorov@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Mareks Kalnačs @ 2018-09-13 10:03 UTC (permalink / raw)
To: Victor Yegorov <vyegorov@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs; Māris Rucis <Maris.Rucis@datakom.lv>; Pāvels Koržs <Pavels.Korzs@datakom.lv>
Hi!
But we are using value indexes not a json index:
CREATE INDEX idx_oss_alarms_dn
ON oss_alarms
USING btree
((jdata ->> 'dn'::text) COLLATE pg_catalog."default");
CREATE INDEX idx_oss_objects_sid
ON oss_objects
USING btree
((jdata ->> 'sid'::text) COLLATE pg_catalog."default");
CREATE UNIQUE INDEX idx_tvc_entity_sid_u
ON tvc_entity
USING btree
((jdata ->> 'siteId'::text) COLLATE pg_catalog."default")
WHERE (jdata ->> 'siteId'::text) IS NOT NULL;
May be we don’t understand this index behavior?
Mareks
From: Victor Yegorov <vyegorov@gmail.com>
Sent: trešdiena, 2018. gada 12. septembris 19:43
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
Cc: pgsql-bugs@postgresql.org; Māris Rucis <Maris.Rucis@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
ср, 12 сент. 2018 г. в 19:21, Mareks Kalnačs <Mareks.Kalnacs@datakom.lv<mailto:Mareks.Kalnacs@datakom.lv>>:
I will disagree with you about bug. I understand that foreign keys will solve some issues and we already making changes add FK, but this only bypass problem, not solve it and in different conditions we got problem again. Main issue, optimizer not take in account index distribution. In current case main cause actually is subselect. I little bit played around with indexes and different select conditions and got very strange behavior. What we done, add new index for tvc_entity table (idx_tvc_entity_sid_u) and run full analyze for all tables and got problem in reverse, now optimizer thinks that he will get less rows from subselect.
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.
That is the reason I outlined, that important keys should be extracted into plain columns.
If you still consider this is a bug, please — send isolated reproducible test case that demonstrates the bug.
--
Victor Yegorov
________________________________
Š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
________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-13 10:19 Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Mareks Kalnaès @ 2018-09-13 10:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-bugs; Mâris Rucis <Maris.Rucis@datakom.lv>; Pâvels Korþs <Pavels.Korzs@datakom.lv>
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
________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-13 17:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2018-09-13 17:42 UTC (permalink / raw)
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-bugs; Māris Rucis <Maris.Rucis@datakom.lv>; Pāvels Koržs <Pavels.Korzs@datakom.lv>
=?utf-8?B?TWFyZWtzIEthbG5hxI1z?= <Mareks.Kalnacs@datakom.lv> writes:
> But we are using value indexes not a json index:
> CREATE INDEX idx_oss_alarms_dn
> ON oss_alarms
> USING btree
> ((jdata ->> 'dn'::text) COLLATE pg_catalog."default");
I think you're outsmarting yourself by including those COLLATE clauses.
They don't do anything, since they're just selecting the default behavior
--- but they're enough to make the planner not realize that stats
collected on the index expression would be applicable to a plain
reference to oss_alarms.jdata ->> 'dn'. In general you want the index
expression to be spelled exactly the same way that you refer to the
value in queries, else the system may not realize it's relevant.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: PostgreSQL 10.0 SELECT LIMIT performance problem
@ 2018-09-14 05:59 Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Mareks Kalnaès @ 2018-09-14 05:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-bugs; Mâris Rucis <Maris.Rucis@datakom.lv>; Pâvels Korþs <Pavels.Korzs@datakom.lv>
Actually Postgres trying to outsmart himself, I do not add COLLATE ;)
Original trigger text
CREATE INDEX idx_oss_alarms_dn ON oss_alarms ((jdata ->> 'dn'));
Mareks
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: ceturtdiena, 2018. gada 13. septembris 20:42
To: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-bugs@postgresql.org; Mâris Rucis <Maris.Rucis@datakom.lv>; Pâvels Korþs <Pavels.Korzs@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
=?utf-8?B?TWFyZWtzIEthbG5hxI1z?= <Mareks.Kalnacs@datakom.lv> writes:
> But we are using value indexes not a json index:
> CREATE INDEX idx_oss_alarms_dn
> ON oss_alarms
> USING btree
> ((jdata ->> 'dn'::text) COLLATE pg_catalog."default");
I think you're outsmarting yourself by including those COLLATE clauses.
They don't do anything, since they're just selecting the default behavior
--- but they're enough to make the planner not realize that stats collected on the index expression would be applicable to a plain reference to oss_alarms.jdata ->> 'dn'. In general you want the index expression to be spelled exactly the same way that you refer to the value in queries, else the system may not realize it's relevant.
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
________________________________
^ permalink raw reply [nested|flat] 13+ messages in thread
* [PATCH v1 1/1] Fix unrecognized node type about statistics on expressions
@ 2022-08-04 14:46 Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Japin Li @ 2022-08-04 14:46 UTC (permalink / raw)
Commit a4d75c86bf supported extended statistics on expressions, however
it checks the column privilege pass a wrong pointer to pull_varattnos().
There has another bug when checking the attribute privilege, since the
bitmap contains the offset by FirstLowInvalidHeapAttributeNumber.
---
src/backend/statistics/extended_stats.c | 7 +++++--
src/test/regress/expected/stats_ext.out | 9 +++++++++
src/test/regress/sql/stats_ext.sql | 10 ++++++++++
3 files changed, 24 insertions(+), 2 deletions(-)
inline patch 1: src/backend/statistics/extended_stats.c, src/test/regress/expected/stats_ext.out, src/test/regress/sql/stats_ext.sql (+24/-2 lines) download inline-1.patch
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index d2aa8d0ca3..a6a312d3de 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -1619,7 +1619,7 @@ statext_is_compatible_clause(PlannerInfo *root, Node *clause, Index relid,
/* Don't have table privilege, must check individual columns */
if (*exprs != NIL)
{
- pull_varattnos((Node *) exprs, relid, &clause_attnums);
+ pull_varattnos((Node *) *exprs, relid, &clause_attnums);
clause_attnums = bms_add_members(clause_attnums, *attnums);
}
else
@@ -1639,7 +1639,10 @@ statext_is_compatible_clause(PlannerInfo *root, Node *clause, Index relid,
while ((attnum = bms_next_member(clause_attnums, attnum)) >= 0)
{
- if (pg_attribute_aclcheck(rte->relid, attnum, userid,
+ /* bit #s are offset by FirstLowInvalidHeapAttributeNumber */
+ AttrNumber attno = attnum + FirstLowInvalidHeapAttributeNumber;
+
+ if (pg_attribute_aclcheck(rte->relid, attno, userid,
ACL_SELECT) != ACLCHECK_OK)
return false;
}
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8f5fd546eb..5f9040ae48 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3251,6 +3251,15 @@ DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- Tidy up
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
+-- Test for bug 17570.
+RESET SESSION AUTHORIZATION;
+CREATE TABLE priv_test_tbl1(a INT, b VARCHAR);
+INSERT INTO priv_test_tbl1(a, b) SELECT i, i FROM generate_series(1,100) s(i);
+CREATE STATISTICS priv_test_tbl1_stats (mcv) ON (a), (b) FROM priv_test_tbl1;
+CREATE ROLE priv_test_user1;
+SET SESSION AUTHORIZATION priv_test_user1;
+SELECT * FROM priv_test_tbl1 WHERE a = 1 AND b = '1';
+ERROR: permission denied for table priv_test_tbl1
RESET SESSION AUTHORIZATION;
DROP SCHEMA tststats CASCADE;
NOTICE: drop cascades to 2 other objects
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 5fd865f509..8a65229c05 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1642,6 +1642,16 @@ DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- Tidy up
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
+
+-- Test for bug 17570.
+RESET SESSION AUTHORIZATION;
+CREATE TABLE priv_test_tbl1(a INT, b VARCHAR);
+INSERT INTO priv_test_tbl1(a, b) SELECT i, i FROM generate_series(1,100) s(i);
+CREATE STATISTICS priv_test_tbl1_stats (mcv) ON (a), (b) FROM priv_test_tbl1;
+CREATE ROLE priv_test_user1;
+SET SESSION AUTHORIZATION priv_test_user1;
+SELECT * FROM priv_test_tbl1 WHERE a = 1 AND b = '1';
+
RESET SESSION AUTHORIZATION;
DROP SCHEMA tststats CASCADE;
DROP USER regress_stats_user1;
--
2.17.1
end of thread, other threads:[~2022-08-04 14:46 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-12 08:31 PostgreSQL 10.0 SELECT LIMIT performance problem Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
2018-09-12 12:31 ` Victor Yegorov <vyegorov@gmail.com>
2018-09-12 16:23 ` Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
2018-09-12 16:43 ` Victor Yegorov <vyegorov@gmail.com>
2018-09-12 17:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-09-12 17:14 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2018-09-13 10:19 ` Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
2018-09-13 10:03 ` Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
2018-09-13 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-09-14 05:59 ` Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
2018-09-12 12:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-09-12 13:16 ` Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
2022-08-04 14:46 [PATCH v1 1/1] Fix unrecognized node type about statistics on expressions Japin Li <japinli@hotmail.com>
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