postgrespro / pgwininstall Goto Github PK

explain analyze select * from t where val like 'зё%';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_idx on t  (cost=0.42..8.44 rows=2268 width=29) (actual time=0.009..0.009 rows=0 loops=1)
   Index Cond: ((val >= 'зё'::mvarchar COLLATE "default") AND (val < 'зє'::mvarchar COLLATE "default"))
   Filter: (val ~~ 'зё%'::mvarchar)
   Heap Fetches: 0
 Planning time: 1.251 ms
 Execution time: 0.028 ms
(6 строк)

select * from t where val like 'зё%';
 val 
-----
(0 строк)

load 'plantuner';
set plantuner.disable_index = 't_idx';
explain analyze select * from t where val like 'зё%';
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..9072.00 rows=2268 width=29) (actual time=69.260..70.382 rows=2 loops=1)
   Filter: (val ~~ 'зё%'::mvarchar)
   Rows Removed by Filter: 453519
 Planning time: 0.241 ms
 Execution time: 70.397 ms
(5 строк)

select * from t where val like 'зё%';
    val     
------------
 Зёма Л. Ф.
 Зё Г. В.
(2 строки)

explain analyze select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_idx on t  (cost=0.42..8.44 rows=1 width=29) (actual time=0.049..0.049 rows=0 loops=1)
   Index Cond: ((val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar))
   Heap Fetches: 0
 Planning time: 0.170 ms
 Execution time: 0.068 ms
(5 строк)

select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
 val 
-----
(0 строк)

load 'plantuner';
set plantuner.disable_index = 't_idx';
explain analyze select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..10205.80 rows=1 width=29) (actual time=95.307..96.933 rows=2 loops=1)
   Filter: ((val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar))
   Rows Removed by Filter: 453519
 Planning time: 0.084 ms
 Execution time: 96.948 ms
(5 строк)

select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
    val     
------------
 Зёма Л. Ф.
 Зё Г. В.
(2 строки)

create extension pageinspect;


select g.g as page, i.* from generate_series(1,2823) as g, lateral bt_page_items('t_idx', g.g) as i
where regexp_replace(data,' ','','g') like '%'||right(mvarchar_send('Зё Г. В.')::text, -2)||'%';
page | itemoffset |   ctid    | itemlen | nulls | vars |                                  data                                   
------+------------+-----------+---------+-------+------+-------------------------------------------------------------------------
  822 |        115 | (3402,13) |      32 | f     | t    | 23 17 04 51 04 20 00 13 04 2e 00 20 00 12 04 2e 00 00 00 00 00 00 00 00
(1 строка)


select * from heap_page_items(get_raw_page('t', 3402)) where lp = 13;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |  t_ctid   | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                t_data                
----+--------+----------+--------+--------+--------+----------+-----------+-------------+------------+--------+--------+-------+--------------------------------------
 13 |   7504 |        1 |     41 |   1018 |      0 |      463 | (3402,13) |           1 |       2306 |     24 |        |       | \x2317045104200013042e00200012042e00
(1 строка)