-- sift.explain_why for a statement with no aggregate: the row "index" says whether the plan reads a sift_col index (index, index-only or bitmap scan) and, if not, why:
-- no_source (the table has none), where (no condition of the statement is one the index answers) or cost (the normal plan is estimated cheaper, with both costs).
CREATE EXTENSION pg_sift;
SET default_statistics_target = 10000;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE ew (id int, k int, grp int, note text);
INSERT INTO ew SELECT i, (hashint4(i) & 1023), i % 10, 'n' || i FROM generate_series(1, 100000) i;
CREATE TABLE ew_plain AS SELECT * FROM ew;
CREATE INDEX ew_col ON ew USING sift_col (id, k, grp);
NOTICE:  sift_col index "ew_col": 1 automatic choice (order_array_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; order_array_columns = id, k
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ew_col');
NOTICE:  sift_col index "ew_col" is column format minor 44, which a library before 4.3 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
VACUUM (FREEZE, ANALYZE) ew;
VACUUM (FREEZE, ANALYZE) ew_plain;
CREATE FUNCTION why(q text) RETURNS TABLE (arm text, used bool, slug text, has_scan_cost bool, has_normal_cost bool, scan_below_normal bool) LANGUAGE sql AS
$$ SELECT arm, used, slug, scan_cost IS NOT NULL, normal_cost IS NOT NULL, scan_cost < normal_cost FROM sift.explain_why(q) $$;
-- an equality on a key of the index: the plan reads it
SELECT * FROM why($q$ SELECT * FROM ew WHERE id = 5 $q$);
  arm  | used | slug | has_scan_cost | has_normal_cost | scan_below_normal 
-------+------+------+---------------+-----------------+-------------------
 index | t    |      | t             | f               | 
(1 row)

-- a table with no sift_col index
SELECT * FROM why($q$ SELECT * FROM ew_plain WHERE id = 5 $q$);
  arm  | used |   slug    | has_scan_cost | has_normal_cost | scan_below_normal 
-------+------+-----------+---------------+-----------------+-------------------
 index | f    | no_source | f             | f               | 
(1 row)

-- a condition on a column the index does not hold, and one the index cannot answer
SELECT * FROM why($q$ SELECT * FROM ew WHERE note = 'n5' $q$);
  arm  | used | slug  | has_scan_cost | has_normal_cost | scan_below_normal 
-------+------+-------+---------------+-----------------+-------------------
 index | f    | where | f             | f               | 
(1 row)

SELECT * FROM why($q$ SELECT * FROM ew WHERE id + k = 5 $q$);
  arm  | used | slug  | has_scan_cost | has_normal_cost | scan_below_normal 
-------+------+-------+---------------+-----------------+-------------------
 index | f    | where | f             | f               | 
(1 row)

-- a condition that keeps most of the table: the sequential scan is cheaper
SELECT * FROM why($q$ SELECT * FROM ew WHERE k >= 0 $q$);
  arm  | used | slug | has_scan_cost | has_normal_cost | scan_below_normal 
-------+------+------+---------------+-----------------+-------------------
 index | f    | cost | t             | t               | f
(1 row)

-- an aggregate keeps its arms and gets no "index" row
SELECT * FROM why($q$ SELECT count(*) FROM ew WHERE grp = 3 $q$);
  arm   | used | slug | has_scan_cost | has_normal_cost | scan_below_normal 
--------+------+------+---------------+-----------------+-------------------
 single | t    |      | t             | t               | t
(1 row)

DROP FUNCTION why(text);
DROP TABLE ew, ew_plain;
DROP EXTENSION pg_sift;