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