This is a two-part bug report. The first part is something that I feel is
an obscure bug, the other would be considered 'optimizer acrobatics'
I have 3 tables: TABLEA, TABLEB, TABLEC
TABLEA has integer columns XID and AID, with a unique index on (XID,AID)
and a regular index on AID
TABLEB has integer columns XID and BID, with a unique index on (XID,BID)
and a regular index on BID
TABLEC has integer columns XID and CID, with a unique index on (XID,CID)
and a regular index on CID.
All three tables have populations in excess of 100,000 rows.
I created a view:
create table_view as
select XID, AID as YID, 'A' as TABLE_TYPE from TABLEA
UNION ALL
select XID, BID as YID, 'B' as TABLE_TYPE from TABLEB
UNION ALL
select XID, CID as YID, 'C' as TABLE_TYPE from TABLEC;
When I do the following query
EXPLAIN select * from table_view
where YID = 17 and TABLE_TYPE = 'A';
I get the following query plan (this is actual output, I changed the
names in the example above):
Subquery Scan test_view (cost=0.00..39088.27 rows=2029327 width=20)
-> Append (cost=0.00..39088.27 rows=2029327 width=20)
-> Subquery Scan *SELECT* 1 (cost=0.00..16857.65 rows=871365
width=20) -> Seq Scan on enrollstudentschool
(cost=0.00..16857.65 rows=871365 width=20)
-> Subquery Scan *SELECT* 2 (cost=0.00..4976.23 rows=286723
width=8)
-> Seq Scan on enrollstudentclassgroup (cost=0.00..4976.23
rows=286723 width=8)
-> Subquery Scan *SELECT* 3 (cost=0.00..17254.39 rows=871239
width=20) -> Seq Scan on enrollstudentdistrict
(cost=0.00..17254.39 rows=871239 width=20)
So, here's the first problem: In all three cases, the optimizer should
have seen that there was an index on AID/BID/CID respectively, and used
that. Instead, it table scanned all three large tables. I suspect this
may be because the optimizer doesn't translate the column name from
view name to table name before looking for a suitable index.
The second part is more feature request than bug report: the optimizer
could have noticed that the TABLE_TYPE value of 'A' never occurs with the
other two tables, and could have avoided querying them entirely. This
feature exists in Oracle, and I've used it to great effect there.
Thanks for your time.
