(text/x-markdown)
In the SQL code below, the first query against an ordinary table runs against
an index and is thus fast. (In real life, the tables are much wider and so
running against the index has a big performance advantage - kind of like using
a column store.) However, the query that is run against the UNION ALL view
does not use the covering index. It runs much slower.
Desired result: The query planner should recognize that the t1.b and t2.c
columns are never used and hence never load them, thus allowing the indexes
on t1.a and t2.a to be covering indexes.
> ~~~~
CREATE TABLE t1(a INT, b INT);
CREATE INDEX t1a ON t1(a);
CREATE TABLE t2(a INT, c INT);
CREATE INDEX t2ac ON t2(a);
CREATE VIEW tv AS SELECT * FROM t1 UNION ALL SELECT * FROM t2;
.eqp on
SELECT count(*) FROM t1 WHERE a=0;
SELECT count(*) FROM tv WHERE a=0;
~~~~
|