One search. Connected ideas.

An index scan follows an index to locate matching rows, while a sequential scan reads the table directly. If a query needs much of a table, following many scattered row locations can cost more than scanning it. PostgreSQL's planner weighs expected row count, I/O and other costs rather than always preferring indexes.

An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.

EXPLAIN shows the execution plan the database expects to use. EXPLAIN ANALYZE actually executes the statement and adds observed rows and timing, which helps reveal bad estimates or expensive steps. Since it runs the statement, write queries and expensive reads need appropriate care.

PostgreSQL keeps older row versions for concurrent readers, so updates and deletes can leave dead versions behind. VACUUM makes eligible dead space reusable and supports visibility and transaction-ID maintenance; ANALYZE collects statistics for planning queries. Autovacuum runs this maintenance in the background but can be limited by long-running transactions or workload settings.

An index scan follows an index to locate matching rows, while a sequential scan reads the table directly. If a query needs much of a table, following many scattered row locations can cost more than scanning it. PostgreSQL's planner weighs expected row count, I/O and other costs rather than always preferring indexes.

An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.

EXPLAIN shows the execution plan the database expects to use. EXPLAIN ANALYZE actually executes the statement and adds observed rows and timing, which helps reveal bad estimates or expensive steps. Since it runs the statement, write queries and expensive reads need appropriate care.

PostgreSQL keeps older row versions for concurrent readers, so updates and deletes can leave dead versions behind. VACUUM makes eligible dead space reusable and supports visibility and transaction-ID maintenance; ANALYZE collects statistics for planning queries. Autovacuum runs this maintenance in the background but can be limited by long-running transactions or workload settings.

SQL & PostgreSQL learning module