Example of current searchClientsByAttributes in main, for a particular client attribute value, providing increasing offset values.
explain analyze select ce1_0.ID
from CLIENT ce1_0
join CLIENT_ATTRIBUTES a1_0
on ce1_0.ID=a1_0.CLIENT_ID
where ce1_0.REALM_ID='redacted'
and a1_0.NAME='redacted'
and substr(a1_0.VALUE,1,255)=substr('true',1,255)
and a1_0.VALUE='true'
order by ce1_0.CLIENT_ID
offset 1 rows fetch first 2 rows only; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=293.89..879.47 rows=2 width=73) (actual time=0.082..0.095 rows=2 loops=1)
-> Nested Loop (cost=1.10..4051928.03 rows=13839 width=73) (actual time=0.033..0.094 rows=3 loops=1)
-> Index Scan using idx_315318_uk_b71cjlbenv945rb6gcon438at on client ce1_0 (cost=0.55..678915.15 rows=500133 width=73) (actual time=0.020..0.062 rows=3 loops=1)
Index Cond: ((realm_id)::text = 'redacted'::text)
-> Index Scan using idx_315336_primary on client_attributes a1_0 (cost=0.56..6.74 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=3)
Index Cond: (((client_id)::text = (ce1_0.id)::text) AND ((name)::text = 'redacted'::text))
Filter: ((value = 'true'::text) AND (substr(value, 1, 255) = 'true'::text))
Planning Time: 0.614 ms
Execution Time: 0.121 ms
(9 rows)
A small offset specified causes the query plan to utilize the indices effectively.
explain analyze select ce1_0.ID
from CLIENT ce1_0
join CLIENT_ATTRIBUTES a1_0
on ce1_0.ID=a1_0.CLIENT_ID
where ce1_0.REALM_ID='redacted'
and a1_0.NAME='redacted'
and substr(a1_0.VALUE,1,255)=substr('true',1,255)
and a1_0.VALUE='true'
order by ce1_0.CLIENT_ID
offset 1000 rows fetch first 2 rows only; QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
Limit (cost=151519.25..151820.28 rows=2 width=73) (actual time=12.742..14.879 rows=2 loops=1)
-> Gather Merge (cost=1001.13..2084021.39 rows=13839 width=73) (actual time=5.059..14.832 rows=1002 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=1.10..2081424.00 rows=5766 width=73) (actual time=0.045..6.141 rows=335 loops=3)
-> Parallel Index Scan using idx_315318_uk_b71cjlbenv945rb6gcon438at on client ce1_0 (cost=0.55..675997.70 rows=208389 width=73) (actual time=0.022..2.737 rows=338 loops=3
)
Index Cond: ((realm_id)::text = 'redacted'::text)
-> Index Scan using idx_315336_primary on client_attributes a1_0 (cost=0.56..6.74 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=1014)
Index Cond: (((client_id)::text = (ce1_0.id)::text) AND ((name)::text = 'redacted'::text))
Filter: ((value = 'true'::text) AND (substr(value, 1, 255) = 'true'::text))
Planning Time: 0.288 ms
Execution Time: 14.918 ms
(12 rows)
As greater offsets are provided, the DB must do scans over the index results, requiring additional execution time.
explain analyze select ce1_0.ID
from CLIENT ce1_0
join CLIENT_ATTRIBUTES a1_0
on ce1_0.ID=a1_0.CLIENT_ID
where ce1_0.REALM_ID='redacted'
and a1_0.NAME='redacted'
and substr(a1_0.VALUE,1,255)=substr('true',1,255)
and a1_0.VALUE='true'
order by ce1_0.CLIENT_ID
offset 10000 rows fetch first 2 rows only; QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=256025.24..256025.47 rows=2 width=73) (actual time=2315.872..2328.133 rows=2 loops=1)
-> Gather Merge (cost=254858.49..256203.98 rows=11532 width=73) (actual time=2309.449..2327.715 rows=10002 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=253858.47..253872.88 rows=5766 width=73) (actual time=2305.774..2306.225 rows=3657 loops=3)
Sort Key: ce1_0.client_id
Sort Method: top-N heapsort Memory: 3114kB
Worker 0: Sort Method: top-N heapsort Memory: 3128kB
Worker 1: Sort Method: top-N heapsort Memory: 3123kB
-> Nested Loop (cost=1215.15..253498.28 rows=5766 width=73) (actual time=143.732..2104.580 rows=161385 loops=3)
-> Parallel Bitmap Heap Scan on client_attributes a1_0 (cost=1214.60..207602.91 rows=5768 width=37) (actual time=143.700..560.355 rows=161385 loops=3)
Recheck Cond: (((name)::text = 'redacted'::text) AND (substr(value, 1, 255) = 'true'::text))
Filter: (value = 'true'::text)
Heap Blocks: exact=98083
-> Bitmap Index Scan on idx_client_att_by_name_value (cost=0.00..1211.14 rows=83071 width=0) (actual time=67.206..67.206 rows=502674 loops=1)
Index Cond: (((name)::text = 'redacted'::text) AND (substr(value, 1, 255) = 'true'::text))
-> Index Scan using idx_315318_primary on client ce1_0 (cost=0.55..7.96 rows=1 width=73) (actual time=0.009..0.009 rows=1 loops=484154)
Index Cond: ((id)::text = (a1_0.client_id)::text)
Filter: ((realm_id)::text = 'redacted'::text)
Planning Time: 0.296 ms
Execution Time: 2328.706 ms
(21 rows)
At large offset values, the query plan changes to heap scan, greatly negatively affecting the query cost and execution time.