I understand basic primary key table storage in PostgreSQL, where the data is stored in pages, and a B-tree index structure is used for efficient retrieval.
However, I would like to understand the behaviour when a table doesn't have a primary key but has an index on a non-primary key column, such as the
CREATE TABLE products (
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
Assuming the "products" table has 100 rows, my understanding is that the data will be stored in pages, and the B-tree index will have a root node
Additionally, there will be intermediate nodes with ranges like
[1, 49] and
[50, 100], which will have child nodes, and so on. However, I'm not clear about what will happen with the leaf nodes of the index. Will they contain actual row data?
In the case of an index on the
namecolumn without a primary key, will the leaf nodes of the index contain the actual row data or pointers to the rows?
How will the retrieval process work if the leaf nodes contain the actual row data? Will it perform a linear scan of the leaf nodes to find the desired "name" value?
Also, can having a primary key, if the table is already indexed on
name, optimize the query?
I would appreciate a detailed explanation of this scenario's storage and retrieval mechanism, along with any relevant code examples or additional considerations.