Re: Index Onlys Scan for expressions - Mailing list pgsql-hackers
| From | Vladimir Sitnikov |
|---|---|
| Subject | Re: Index Onlys Scan for expressions |
| Date | |
| Msg-id | CAB=Je-F_soyV5NQATsOcmhYz-LHMdfXhs=F1WoMaYDWnV3uddw@mail.gmail.com Whole thread Raw |
| In response to | Re: Index Onlys Scan for expressions (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Index Onlys Scan for expressions
(Ildar Musin <i.musin@postgrespro.ru>)
|
| List | 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
pgsql-hackers by date: