From: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
To: Victor Yegorov <vyegorov@gmail.com>
Cc: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Cc: Māris Rucis <Maris.Rucis@datakom.lv>
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
Date: Wed, 12 Sep 2018 16:23:22 +0000
Message-ID: <e65c26cc8fae43abbad69b3e51d1579d@datakom.lv> (raw)
In-Reply-To: <CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
<CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
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) downloadview imagetechnical-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
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, vyegorov@gmail.com, Maris.Rucis@datakom.lv
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <e65c26cc8fae43abbad69b3e51d1579d@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