On Thu, 1 Aug 2002, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > You want to be careful with this sort of stuff, since the query planner
> > sometimes won't do the view as efficiently as it would do the fully
> > specified equivalant query. I've posted about this here before.
>
> Please provide an example. AFAIK a view is a query macro, and nothing
> else.
I already did provide an example, and you even replied to it. :-)
See the appended message.
BTW, this page
http://archives.postgresql.org/pgsql-general/2002-06/threads.php
does not display in Navigator 4.78. Otherwise I would have provided a
reference to the thread in the archive.
Maybe we need a web based form for reporting problem pages in the archives.
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
>From [EMAIL PROTECTED] Thu Aug 1 13:14:23 2002
Date: Mon, 3 Jun 2002 18:18:52 +0900 (JST)
From: Curt Sampson <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: View vs. Statement Query Plan
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 <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
