Where clause: indexes - equality

explain analyze SELECT first_name, last_name
FROM employees WHERE employee_id = 123;
      

Index Scan using employees_pkey on employees
    (cost=0.29..8.31 rows=1 width=16)
    (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (employee_id = 123)
Planning time: 0.310 ms
Execution time: 0.051 ms
      

Where clause: indexes - multiple columns

explain analyze SELECT first_name, last_name
FROM employees WHERE employee_id = 111
    and subsidiary_id = 333;
      

Index Scan using employees_employee_id_subsidiary_id_idx
    on employees (cost=0.29..8.31 rows=1 width=16)
    (actual time=0.023..0.025 rows=1 loops=1)
Index Cond: ((employee_id = 111) AND (subsidiary_id = 333))
Planning time: 0.130 ms
Execution time: 0.056 ms
      

Where clause: indexes - functions

explain analyze SELECT first_name, last_name
FROM employees WHERE upper(last_name) = 'LN 38'
      

Seq Scan on employees
    (cost=0.00..2334.00 rows=500 width=16)
    (actual time=0.048..46.647 rows=1 loops=1)
Filter: (upper(last_name) = 'LN 38'::text)
Rows Removed by Filter: 99999
Planning time: 0.092 ms
Execution time: 46.672 ms
      

Where clause: indexes - functions

explain analyze SELECT first_name, last_name
FROM employees WHERE upper(last_name) = 'LN 38'
      

Bitmap Heap Scan on employees
    (cost=12.29..775.05 rows=500 width=16)
    (actual time=0.070..0.070 rows=1 loops=1)
  Recheck Cond: (upper(last_name) = 'LN 38'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on employees_upper_last_name
            (cost=0.00..12.17 rows=500 width=0)
            (actual time=0.065..0.065 rows=1 loops=1)
        Index Cond: (upper(last_name) = 'LN 38'::text)
Planning time: 0.360 ms
Execution time: 0.091 ms
      

The Join Operation: Nested Loops

• The right relation is scanned once for every row found in the left one
• Analogous to ORMs N+1 issue. Corollary: get to know your ORM and take control of joins
• Requires indexing of the join columns

• Good performance if the driving query returns a small result set
• Many B-tree traversals when executing the inner query

The Join Operation: Hash Join

• The right relation is first scanned and loaded into a hash table, using its join attributes as hash keys. Next the left relation is scanned and the appropriate values of every row found are used as hash keys to locate the matching rows in the table
• You can improve hash join performance by selecting fewer columns
• There is no need to index the join columns. Indexing independent where predicates improve performance

• Only one scan per table
• Symmetric

The Join Operation: Sort Merge

• Each relation is sorted on the join attributes before the join starts. Then the two relations are scanned in parallel, and matching rows are combined to form join rows
• Only one scan per table

• Good for outer joins
• Good if input is sorted

Clustering Data: Index Filter Predicates

CREATE INDEX empsubupnam ON employees
    (subsidiary_id, UPPER(last_name));
      

--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM|   17 |    3 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')
      

Clustering Data: Index-only scan

CREATE INDEX empsubupnam ON employees
    (subsidiary_id, last_name);
      

explain select subsidiary_id, last_name
    from employees where subsidiary_id < 5;
      

Index Only Scan using empsubupnam on employees
      (cost=0.42..170.54 rows=5150 width=12)
      (actual time=0.109..0.912 rows=5000 loops=1)
 Index Cond: (subsidiary_id < 5)
 Heap Fetches: 0
Planning time: 0.091 ms
Execution time: 1.244 ms
      

Sorting and grouping

create index sales_date on sales(sale_date);
      

explain analyze select * from sales order by sale_date;
      

Index Scan using sales_date on sales
    (cost=0.42..29120.43 rows=900000 width=24)
    (actual time=0.024..247.604 rows=900000 loops=1)
Planning time: 0.118 ms
Execution time: 315.568 ms
      

Sorting and grouping

• Execution time: 1074.673 ms -> 315.568 ms :-) (x0.3)
• Planning time: 0.112 ms -> 0.118 ms :-| (~=)
• cost=122200.04..124450.04 -> 0.42..29120.43 :-) (x0.2)

Sorting and grouping

• Execution time: 1074.673 ms -> 315.568 ms :-) (x0.3)
• Planning time: 0.112 ms -> 0.118 ms :-| (~=)
• cost=122200.04..124450.04 -> 0.42..29120.43 :-) (x0.2)

TL;DR: cost is not always an accurate measure

For a query that requires scanning a large fraction of the table, an explicit sort is likely to be faster than using an index because it requires less disk I/O due to following a sequential access pattern

Cost can increase because clustering factor of the new index is worse

Sorting and grouping: grouping

• Temporary hash
• Sort/group

• With already sorted indexes

Partial results: retrieving top-n

• Fetch only the first rows of a query and then closing the statement - the optimizer cannot foresee that when preparing the execution plan

Partial results: retrieving top-n

• Fetch only the first rows of a query and then closing the statement - the optimizer cannot foresee that when preparing the execution plan

LIMIT { count | ALL }
OFFSET start
      

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
      

Modifying data: insert

1. Store the row in any block with free space
2. Update indexes
   1. Find the correct leaf node
   2. Split leaf node if it hasn't free space

• Add the entry to each and every index
• Balance tree on split

Modifying data: delete

1. Find matches
2. Delete the row
3. Update indexes

• Takes advantage of indexes for search
• Removes the entry from each and every index
• Balance the tree on compacting

Execution plans

PREPARE stmt(int) AS SELECT $1;
      

EXPLAIN EXECUTE stmt(1);
      

Execution plans

PREPARE stmt(int) AS SELECT $1;
      

EXPLAIN EXECUTE stmt(1);
      

Result  (cost=0.00..0.01 rows=1 width=0)
      

0.00

Startup cost

0.01

Total cost for the execution if all rows are retrieved

Execution plans

PREPARE stmt(int) AS SELECT $1;
      

EXPLAIN EXECUTE stmt(1);
      

Result  (cost=0.00..0.01 rows=1 width=0)
      

0.00

Startup cost

0.01

Total cost for the execution if all rows are retrieved

DEALLOCATE stmt;
      

Execution plans: Distinguishing Access and Filter-Predicates

CREATE TABLE scale_data (
     section NUMERIC NOT NULL,
     id1     NUMERIC NOT NULL,
     id2     NUMERIC NOT NULL
  );
CREATE INDEX scale_data_key ON scale_data(section, id1);
      

Execution plans: Distinguishing Access and Filter-Predicates

PREPARE stmt(int) AS SELECT count(*)
                         FROM scale_data
                        WHERE section = 1
AND id2 = $1; EXPLAIN EXECUTE stmt(1);
      

 Aggregate  (cost=529346.31..529346.32 rows=1 width=0)
    Output: count(*)
    -> Index Scan using scale_data_key on scale_data
       (cost=0.00..529338.83 rows=2989 width=0)
       Index Cond: (scale_data.section = 1::numeric)
       Filter: (scale_data.id2 = ($1)::numeric)
      

Execution plans: Distinguishing Access and Filter-Predicates

CREATE INDEX scale_slow
            ON scale_data (section, id1, id2);
      

Aggregate (cost=14215.98..14215.99 rows=1 width=0)
    Output: count(*)
-> Index Scan using scale_slow on scale_data
(cost=0.00..14208.51 rows=2989 width=0)
Index Cond: (section = 1::numeric AND id2 = ($1)::numeric)