I am trying to understand how Postgres uses index and ran into a surprising
behavior if someone can help me with. I have a table like so:
CREATE TABLE testschema.employees ( employee_id integer not null,
first_name varchar(1000) not null, last_name varchar(1000) not null,
date_of_birth date not null, phone_number varchar(1000) not null, constraint
employees_pk primary key (employee_id) );
At the moment I have ZERO rows in the table. I verified that employees_pk
creates an index of type 'btree(employee_id). After that I ran explain on two
queries:
(1) Here I am searching for employee_id '123' and as expected the plan shows it
will do an b-tree traversal on employees_pk index.
testdb# explain select first_name, last_name from testschema.employees where
employee_id = 123; QUERY PLAN
═════════════════════════════════════════════════════════════════════════════════
Index Scan using employees_pk on employees (cost=0.14..8.16 rows=1
width=1032) Index Cond: (employee_id = 123)(2 rows)
(2) Here I am searching for employee_id < 123. I was expecting the plan would
use the index on employees_pk to find all leaf nodes where employee_id < 123
and then issue read of table blocks for each of the matching entries in the
index leaf. But looks like the query plan has decided on using full table scan
instead and not using the index. Any ideas why is this happening?
testdb# explain select first_name, last_name from testschema.employees where
employee_id < 123; QUERY PLAN
══════════════════════════════════════════════════════════════ Seq Scan on
employees (cost=0.00..10.62 rows=17 width=1032) Filter: (employee_id <
123)(2 rows)
Any help will be greatly appreciated.
Thanks,
Shantanu
