PostgreSQL Example Scripts for “3-Minute Test”

This section contains the create, insert and select statements for the “Test your SQL Know-How in 3 Minutes” test. You may want to test yourself before reading this page.

The create and insert statements are available in the example schema archive.

The execution plans shown are abbreviated for better readability.

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017;

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-01-01'
   AND date_column <  DATE'2018-01-01';

The first execution plan performs a full table scan (Seq Scan). The second execution plan, on the other hand, uses the index.

                    QUERY PLAN
----------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=3
-> Seq Scan on tbl (actual rows=271 loops=1)
   Filter: (date_part('year', date_column) = '2017')
   Rows Removed by Filter: 29
   Buffers: shared hit=3

                  QUERY PLAN
------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=3
-> Seq Scan on tbl (actual rows=271 loops=1)
   Filter: ((date_column >= '2017-01-01'::date)
        AND (date_column <  '2018-01-01'::date))
   Rows Removed by Filter: 29
   Buffers: shared hit=3

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 FETCH FIRST 1 ROW ONLY;

The query uses the index (Index Scan) and fetches in reverse order (Backward). Note that there is no sort operation.

                            QUERY PLAN
-------------------------------------------------------------------
Limit (actual rows=1 loops=1)
Buffers: shared hit=3
-> Index Scan Backward using tbl_idx on tbl (actual rows=1 loops=1)
   Index Cond: (a = '12'::numeric)
   Buffers: shared hit=3

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

DROP INDEX tbl_idx ;

CREATE INDEX tbl_idx ON tbl (b, a);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

The first query can use both indexes efficiently:

                       QUERY PLAN
--------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: ((a = '38'::numeric) AND (b = '1'::numeric))
Buffers: shared hit=3

                       QUERY PLAN
--------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: ((b = '1'::numeric) AND (a = '38'::numeric))
Buffers: shared hit=3

The second query reads the entire table (Seq Scan). Changing the column order in the index allows both queries to use a (Bitmap) Index Scan.

              QUERY PLAN
---------------------------------------
Seq Scan on tbl (actual rows=2 loops=1)
Filter: (b = '1'::numeric)
Rows Removed by Filter: 298
Buffers: shared hit=11

                      QUERY PLAN
-------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=2 loops=1)
Index Cond: (b = '1'::numeric)
Buffers: shared hit=4

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops);

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%';

The execution plan states that it is doing an Index Scan. Since there is only wild card character at the very end, the full search text 'TJ' can be used as index access predicate.

The PostgreSQL execution plan does not immediately reveal whether the index access is a full index scan or an index range scan. To be safe, you can increase the table size (like 100-fold) and check the number of processed blocks for this query (explain (analyse, buffers)).

                      QUERY PLAN
-------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: (((text)::text ~>=~ 'TJ'::text)
         AND ((text)::text ~<~ 'TK'::text))
Filter: ((text)::text ~~ 'TJ%'::text)
Buffers: shared hit=3

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column);

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

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

Although the index is used efficiently in both queries, the first query does perform an Index Only Scan. That means the second query performs less efficient as the first.

                          QUERY PLAN
---------------------------------------------------------------
GroupAggregate (actual rows=3 loops=1)
Group Key: date_column
Buffers: shared hit=3
-> Index Only Scan using tbl_idx on tbl (actual rows=3 loops=1)
   Index Cond: (a = '12'::numeric)
   Heap Fetches: 0
   Buffers: shared hit=3

                         QUERY PLAN
-------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Group Key: date_column
Buffers: shared hit=5
-> Sort (actual rows=1 loops=1)
   Sort Key: date_column
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=5
   -> Bitmap Heap Scan on tbl (actual rows=1 loops=1)
      Recheck Cond: (a = '38'::numeric)
      Filter: (b = '1'::numeric)
      Rows Removed by Filter: 2
      Heap Blocks: exact=3
      Buffers: shared hit=5
      -> Bitmap Index Scan on tbl_idx (actual rows=3 loops=1)
         Index Cond: (a = '38'::numeric)
         Buffers: shared hit=2