From: | Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> |
---|---|
To: | Ildar Musin <i(dot)musin(at)postgrespro(dot)ru> |
Cc: | Oleg Bartunov <obartunov(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> |
Subject: | Re: Index Onlys Scan for expressions |
Date: | 2016-08-23 20:35:29 |
Message-ID: | CAB=Je-F_soyV5NQATsOcmhYz-LHMdfXhs=F1WoMaYDWnV3uddw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I've tried your indexonlypatch5.patch against REL9_6_BETA3.
Here are some results.
TL;DR:
1) <<where type=42 and upper(vc) like '%ABC%'>> does not support index-only
scan for index (type, upper(vc) varchar_pattern_ops).
3) <<(... where type=42 offset 0) where upper_vc like '%ABC%'>> does
trigger index-only scan. IOS reduces number of buffers from 977 to 17 and
that is impressive.
Can IOS be used for simple query like #1 as well?
Here are the details.
drop table vlsi;
create table vlsi(type numeric, vc varchar(500));
insert into vlsi(type,vc) select round(x/1000),
md5('||x)||md5('||x+1)||md5(''||x+2) from generate_series(1,1000000) as
s(x);
create index type_vc__vlsi on vlsi(type, upper(vc) varchar_pattern_ops);
vacuum analyze vlsi;
0) Smoke test (index only scan works when selecting indexed expression):
explain (analyze, buffers) select type, upper(vc) from vlsi where type=42;
Index Only Scan using type_vc__vlsi on vlsi (cost=0.55..67.97 rows=971
width=36) (actual time=0.012..0.212 rows=1000 loops=1)
Index Cond: (type = '42'::numeric)
Heap Fetches: 0
Buffers: shared hit=17
Planning time: 0.112 ms
Execution time: 0.272 ms
1) When trying to apply "like condition", index only scan does not work.
Note: "buffers hit" becomes 977 instead of 17.
explain (analyze, buffers) select type, upper(vc) from vlsi where type=42
and upper(vc) like '%ABC%';
Index Scan using type_vc__vlsi on vlsi (cost=0.55..1715.13 rows=20
width=36) (actual time=0.069..1.343 rows=23 loops=1)
Index Cond: (type = '42'::numeric)
Filter: (upper((vc)::text) ~~ '%ABC%'::text)
Rows Removed by Filter: 977
Buffers: shared hit=939
Planning time: 0.104 ms
Execution time: 1.358 ms
Mere "subquery" does not help: still no index-only scan
2) explain (analyze, buffers) select * from (select type, upper(vc)
upper_vc from vlsi where type=42) as x where upper_vc like '%ABC%';
Index Scan using type_vc__vlsi on vlsi (cost=0.55..1715.13 rows=20
width=36) (actual time=0.068..1.344 rows=23 loops=1)
Index Cond: (type = '42'::numeric)
Filter: (upper((vc)::text) ~~ '%ABC%'::text)
Rows Removed by Filter: 977
Buffers: shared hit=939
Planning time: 0.114 ms
Execution time: 1.357 ms
3) "offset 0" trick does help:
explain (analyze, buffers) select * from (select type, upper(vc) upper_vc
from vlsi where type=42 offset 0) as x where upper_vc like '%ABC%';
Subquery Scan on x (cost=0.55..80.11 rows=39 width=36) (actual
time=0.033..0.488 rows=23 loops=1)
Filter: (x.upper_vc ~~ '%ABC%'::text)
Rows Removed by Filter: 977
Buffers: shared hit=17
-> Index Only Scan using type_vc__vlsi on vlsi (cost=0.55..67.97
rows=971 width=36) (actual time=0.015..0.210 rows=1000 loops=1)
Index Cond: (type = '42'::numeric)
Heap Fetches: 0
Buffers: shared hit=17
Planning time: 0.086 ms
Execution time: 0.503 ms
Vladimir
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2016-08-23 20:44:01 | Re: [RFC] Change the default of update_process_title to off |
Previous Message | Martín Marqués | 2016-08-23 20:34:19 | Re: pg_dump with tables created in schemas created by extensions |