SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
Which indexes should I know?
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 vs EXPLAIN ANALYZE?
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.
Is an index scan always faster than a sequential scan?
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.
What do VACUUM, ANALYZE and autovacuum do?
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.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.