CODEWITHSUNDEEP
postgresqlindexing
William Añez
William Añez

Reputation: 790

losing indexing in postgresql

I run into a rare situation when creating and deleting indexes on a table, let say that we create the following table:

create table my_table(id serial primary key, label text);

As this table contains a primary key we got our first index, in top of that lets add another one:

create index my_index on my_table(label);

And now some data...

insert into my_table (label) values ('alpha');
insert into my_table (label) values ('beta');
insert into my_table (label) values ('sigma');
insert into my_table (label) values ('sigma');
insert into my_table (label) values ('alpha');
insert into my_table (label) values ('gamma');

At this point we have two indexes working perfectly as we can see on the following sql explains

explain analyze select * from my_table where label = 'alpha';

Bitmap Heap Scan on my_table  (cost=4.20..13.67 rows=6 width=36) 
                              (actual time=0.022..0.022 rows=2 loops=1)
  Recheck Cond: (label = 'alpha'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on my_index  (cost=0.00..4.20 rows=6 width=0) 
                                     (actual time=0.016..0.016 rows=2 loops=1)
        Index Cond: (label = 'alpha'::text)
Planning time: 0.084 ms
Execution time: 0.046 ms

The issue is that if I touch the indexes at this point either by adding a third one or recreating my_index I get painful sequential scans, for example:

drop index my_index;
create index my_index on my_table(label);

-- executing the same search againg
explain analyze select * from my_table where label = 'alpha';

Seq Scan on my_table  (cost=0.00..1.08 rows=1 width=36) 
                      (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (label = 'alpha'::text)
  Rows Removed by Filter: 4
Planning time: 0.111 ms
Execution time: 0.036 ms

My understanding is that whenever I create an index the data on the table should be indexed -locking all write operations in the mean time- and that is not happening for some reason I ignore, does anyone know why this is ?

Thanks in advance,

Upvotes: 1

Views: 284

Answers (1)

klin
klin

Reputation: 121574

You cannot draw any conclusions about the use of indexes in a table with several rows. Prepare test data with at least a few hundred thousand rows, e.g.:

drop table if exists my_table cascade;
create table my_table(id serial primary key, label text);
create index my_index on my_table(label);
insert into my_table (label)
select generate_series(1, 500000)::text

Update the table statistics before applying explain: 

vacuum analyze my_table;

Run explain:

explain analyze select * from my_table where label = '123456';

                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Index Scan using my_index on my_table  (cost=0.42..8.44 rows=1 width=10) (actual time=0.288..0.290 rows=1 loops=1)
   Index Cond: (label = '123456'::text)
 Planning time: 80.753 ms
 Execution time: 0.476 ms
(4 rows)

Upvotes: 1

Related Questions