Before I Show You the Result…

Please submit your answers for analysis

Question 5

This question is different. First consider the following index and query:

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
FROM tbl
WHERE a = 38
GROUP BY date_column

Let's say this query returns at least a few rows.

To implement a new functional requirement, another condition (b = 1) is added to the where clause:

SELECT date_column, count(*)
FROM tbl
WHERE a = 38
AND b = 1
GROUP BY date_column

How will the change affect performance:

Same: Query performance stays about the same

Wrong! The query will be slower.

The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn't need to access the actual table at all.

Accessing any column that is not part of the index prevents this optimization so that the database must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

Tip

Use an index-only scan for queries that access many rows but only a few columns.

Wrong. The provided information is sufficient to tell that the query will be slower.

The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn't need to access the actual table at all.

Accessing any column that is not part of the index prevents this optimization so that the database must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

Tip

Use an index-only scan for queries that access many rows but only a few columns.

The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn't need to access the actual table at all.

Accessing any column that is not part of the index prevents this optimization so that the database must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

Tip

Use an index-only scan for queries that access many rows but only a few columns.

The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn't need to access the actual table at all.

Accessing any column that is not part of the index prevents this optimization so that the database must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

Tip

Use an index-only scan for queries that access many rows but only a few columns.

Although like expressions starting with a wild card character (% or _) cannot use this index efficiently, a pattern that has the wild card character at the very end can! Even if the wild card character is in the middle, the index is still useful.

Although like expressions starting with a wild card character (% or _) cannot use this index efficiently, a pattern that has the wild card character at the very end can! Even if the wild card character is in the middle, the index is still useful.

The index covers the first query only, the second query cannot use the index efficiently.

Note that the database could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.

Changing the index column order makes the index suitable for both queries—without additional overhead. The index should therefore look like this (columns exchanged):

CREATE INDEX tbl_idx ON tbl (b, a)

Tip

Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.

The index covers the first query only, the second query cannot use the index efficiently.

Note that the database could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.

Changing the index column order makes the index suitable for both queries—without additional overhead. The index should therefore look like this (columns exchanged):

CREATE INDEX tbl_idx ON tbl (b, a)

Tip

Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.

Tip

Use this pattern to optimize queries for the latest, best, ….

The trick is that the index supports the where clause as well as the order by clause. The database uses the index to find the last entry that matches the where clause and takes it as result. Even though there is an order by clause, there is no need to sort any rows.

Tip

Use this pattern to optimize queries for the latest, best, ….

The trick is that the index supports the where clause as well as the order by clause. The database uses the index to find the last entry that matches the where clause and takes it as result. Even though there is an order by clause, there is no need to sort any rows.

Wrapping the table column in a function renders this index mostly useless for this query.

Note that the database could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.

Learn More

Wrapping the table column in a function renders this index mostly useless for this query.

Note that the database could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.