DROP TABLE CREATE TABLE CREATE INDEX INSERT 0 27249 magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 3 | 1 | 3 | 1 (1 row) CHECKPOINT INSERT 0 1 magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 231 | 2 | 231 | 2 (1 row) itemoffset | ctid | itemlen | nulls | vars | data ------------+---------+---------+-------+------+------------------------------------------------------------------------- 1 | (71,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 31 39 30 36 36 00 00 2 | (1,1) | 8 | f | f | 3 | (229,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 31 30 36 00 00 4 | (228,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 32 32 36 00 00 5 | (227,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 33 34 36 00 00 6 | (226,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 34 36 36 00 00 7 | (225,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 35 38 36 00 00 8 | (224,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 37 30 36 00 00 9 | (223,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 38 32 36 00 00 (9 rows) -- Visible tuples (name = HIKEY) explain select * from test where name = 'PostgreSQL-0000000147'; QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-0000000147'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-0000000147'::text) (4 rows) select * from test where name = 'PostgreSQL-0000000147'; name ----------------------- PostgreSQL-0000000147 (1 row) -- Still visible tuples (name = HIKEY) explain select * from test where name = 'PostgreSQL-0000000146'; QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-0000000146'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-0000000146'::text) (4 rows) select * from test where name = 'PostgreSQL-0000000146'; name ----------------------- PostgreSQL-0000000146 (1 row) -- They are vislble when no indexes are used explain select * from test where name like '%PostgreSQL-0000000146'; QUERY PLAN ------------------------------------------------------- Seq Scan on test (cost=0.00..458.93 rows=1 width=32) Filter: (name ~~ '%PostgreSQL-0000000146'::text) (2 rows) select * from test where name like '%PostgreSQL-0000000146'; name ----------------------- PostgreSQL-0000000146 (1 row) ########################################################### # Server crashed ########################################################### pg_ctl: another server might be running; trying to start server anyway waiting for server to start... done server started magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 231 | 2 | 231 | 2 (1 row) itemoffset | ctid | itemlen | nulls | vars | data ------------+---------+---------+-------+------+------------------------------------------------------------------------- 1 | (71,1) | 8 | f | f | 2 | (1,1) | 8 | f | f | 3 | (229,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 31 30 36 00 00 4 | (228,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 32 32 36 00 00 5 | (227,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 33 34 36 00 00 6 | (226,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 34 36 36 00 00 7 | (225,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 35 38 36 00 00 8 | (224,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 37 30 36 00 00 9 | (223,1) | 32 | f | t | 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 38 32 36 00 00 (9 rows) -- Visible tuples (name = HIKEY) explain select * from test where name = 'PostgreSQL-0000000147'; QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-0000000147'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-0000000147'::text) (4 rows) select * from test where name = 'PostgreSQL-0000000147'; name ------ (0 rows) -- They become invisible! (name < HIKEY) explain select * from test where name = 'PostgreSQL-0000000146'; QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-0000000146'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-0000000146'::text) (4 rows) select * from test where name = 'PostgreSQL-0000000146'; name ------ (0 rows) -- Without index, they're still visible. explain select * from test where name like '%PostgreSQL-0000000146'; QUERY PLAN ------------------------------------------------------- Seq Scan on test (cost=0.00..458.93 rows=1 width=32) Filter: (name ~~ '%PostgreSQL-0000000146'::text) (2 rows) select * from test where name like '%PostgreSQL-0000000146'; name ----------------------- PostgreSQL-0000000146 (1 row)