The SQL query filters records using a WHERE clause on the inspectorname column and then sorts the results with an ORDER BY clause on the inspectiondate column. The most effective way to optimize this query is with a composite (multi-column) index. The order of columns in the index is critical. The index should first list the column used for filtering (inspectorname) and then the column used for sorting (inspectiondate). This structure allows the database engine to efficiently locate the filtered rows and retrieve them in the pre-sorted order directly from the index, thereby avoiding a costly separate sorting operation.

B. facilitytype, inspectiondate: This index is not useful because it does not include the inspectorname column, which is the primary filter in the WHERE clause.

C. facilitytype, inspectorname: This index does not include the inspectiondate column, forcing the database to perform a separate, inefficient sort operation after filtering the data.

1. Esri ArcGIS Pro Documentation, "Attribute indexes": "An attribute index is an alternate path used by the database management system (DBMS) to retrieve a record from a table... You can create a multicolumn index if you frequently specify two or three fields in a query." This establishes the principle of using multi-column indexes for complex queries. The optimal column order is a fundamental database concept applied here.

Source: Esri ArcGIS Pro Documentation, "Attribute indexes in the geodatabase".

2. PostgreSQL 14 Documentation, Chapter 11. Indexes, Section 11.3. Multicolumn Indexes: "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns." This supports placing inspectorname first in the index to satisfy the WHERE clause. The documentation further explains that an index can satisfy an ORDER BY clause if the sort order matches the index order.

Source: PostgreSQL 14 Documentation, Chapter 11, Section 11.3.

3. Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill. In the chapter on "Query Optimization," the text explains that an index can be used to retrieve tuples satisfying a WHERE condition and also to retrieve them in a specific order for an ORDER BY clause. For this to happen efficiently, the index key must include the attributes from both clauses in the correct sequence (selection attributes first, then ordering attributes).

Source: Chapter 15, "Query Optimization," Section 15.5, "Using Indexing to Speed up Queries." (Specific page numbers vary by edition, but the concept is a core part of this section).