It seems that my server is happy to use some indices to optimize
access when I do a specific query involving a UNION, but when I
make a view and then query on that view, it doesn't use the indices
any more.
I have two tables that look like this:
CREATE TABLE data (
rec_no INT PRIMARY KEY,
day DATE NOT NULL,
user_id INT NOT NULL,
value INT NOT NULL
) WITHOUT OIDS;
CREATE INDEX data_day ON data (day);
CREATE INDEX data_user_id ON data (user_id);
CREATE INDEX data_value ON data (value);
data_4 has about 10 Mrows, data_4a has about 100 Krows. I created a view,
data, combining these two tables:
CREATE VIEW data AS
SELECT * FROM data_4 UNION ALL SELECT * FROM data_4a
But for some reason this view doesn't use the indices that an
equivalant query uses:
test=# explain select * from data_4 where user_id = 12345 union all select * from data_4a where user_id = 12345;
NOTICE: QUERY PLAN:
Append (cost=0.00..4334.59 rows=1080 width=16)
-> Subquery Scan *SELECT* 1 (cost=0.00..4325.05 rows=1078 width=16)
-> Index Scan using data_4_user_id on data_4 (cost=0.00..4325.05 rows=1078 width=16)
-> Subquery Scan *SELECT* 2 (cost=0.00..9.54 rows=2 width=16)
-> Index Scan using data_4a_user_id on data_4a (cost=0.00..9.54 rows=2 width=16)
EXPLAIN
test=# explain select * from data where user_id = 12345;
NOTICE: QUERY PLAN:
Subquery Scan data (cost=0.00..1638580.00 rows=100100000 width=16)
-> Append (cost=0.00..1638580.00 rows=100100000 width=16)
-> Subquery Scan *SELECT* 1 (cost=0.00..1636943.00 rows=100000000 width=16)
-> Seq Scan on data_4 (cost=0.00..1636943.00 rows=100000000 width=16)
-> Subquery Scan *SELECT* 2 (cost=0.00..1637.00 rows=100000 width=16)
-> Seq Scan on data_4a (cost=0.00..1637.00 rows=100000 width=16)
Any idea why this is? Should I be creating the view in a different way?
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
