By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

Views + UNION ALL = Slow ?

P: n/a
Hello !

I have two tables (which contains individual months' data). One of
them contains 500 thousand records and the other one about 40k, 8
columns. When I do a simple query on them individually it takes
milli-seconds to complete (see gory details below). For some querys I
want to include data from multiple months so I created a view using
Union all. But Using the view it takes 31 Seconds to complete the
"same" query.

I am obviously doing something wrong or using something the wrong way.

Any Ideas ?

/Otto Blomqvist

test=# explain analyze select fid_2 from file_92_904 where fid_4=1024;
NOTICE: QUERY PLAN:

Index Scan using file_92_904_ltn_idx on file_92_904
(cost=0.00..219.90 rows=65 width=4) (actual time=0.49..0.49 rows=0
loops=1)
Total runtime: 0.57 msec

EXPLAIN

test=# explain analyze select fid_2 from file_92_1004 where
fid_4=1024;
NOTICE: QUERY PLAN:

Index Scan using file_92_1004_ltn_idx on file_92_1004
(cost=0.00..4505.20 rows=1197 width=4) (actual time=32.36..32.36
rows=0 loops=1)
Total runtime: 32.46 msec

EXPLAIN

test=# create view twotables as select * from file_92_1004 UNION ALL
Select * from file_92_904;
CREATE

test=# explain analyze select fid_2 from twotables where fid_4=1024;
NOTICE: QUERY PLAN:

Subquery Scan twotables (cost=100000000.00..200023000.53 rows=569553
width=203) (actual time=31590.97..31590.97 rows=0 loops=1)
-> Append (cost=100000000.00..200023000.53 rows=569553 width=203)
(actual time=12.13..30683.67 rows=569553 loops=1)
-> Subquery Scan *SELECT* 1 (cost=100000000.00..100021799.06
rows=540306 width=199) (actual time=12.12..28417.81 rows=540306
loops=1)
-> Seq Scan on file_92_1004
(cost=100000000.00..100021799.06 rows=540306 width=199) (actual
time=12.09..14946.47 rows=540306 loops=1)
-> Subquery Scan *SELECT* 2 (cost=100000000.00..100001201.47
rows=29247 width=203) (actual time=0.19..1525.18 rows=29247 loops=1)
-> Seq Scan on file_92_904
(cost=100000000.00..100001201.47 rows=29247 width=203) (actual
time=0.14..793.34 rows=29247 loops=1)
Total runtime: 31591.34 msec

EXPLAIN
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The difficulty is, that your view-based statement do not make use of any
index. So the query must look at each tuple. It seems, that union all
requires a full scan of the participates relations. I dont know if it is
possible but try to create an index on the view ;-)

Hagen

Otto Blomqvist wrote:
Hello !

I have two tables (which contains individual months' data). One of
them contains 500 thousand records and the other one about 40k, 8
columns. When I do a simple query on them individually it takes
milli-seconds to complete (see gory details below). For some querys I
want to include data from multiple months so I created a view using
Union all. But Using the view it takes 31 Seconds to complete the
"same" query.

I am obviously doing something wrong or using something the wrong way.

Any Ideas ?

/Otto Blomqvist

test=# explain analyze select fid_2 from file_92_904 where fid_4=1024;
NOTICE: QUERY PLAN:

Index Scan using file_92_904_ltn_idx on file_92_904
(cost=0.00..219.90 rows=65 width=4) (actual time=0.49..0.49 rows=0
loops=1)
Total runtime: 0.57 msec

EXPLAIN

test=# explain analyze select fid_2 from file_92_1004 where
fid_4=1024;
NOTICE: QUERY PLAN:

Index Scan using file_92_1004_ltn_idx on file_92_1004
(cost=0.00..4505.20 rows=1197 width=4) (actual time=32.36..32.36
rows=0 loops=1)
Total runtime: 32.46 msec

EXPLAIN

test=# create view twotables as select * from file_92_1004 UNION ALL
Select * from file_92_904;
CREATE

test=# explain analyze select fid_2 from twotables where fid_4=1024;
NOTICE: QUERY PLAN:

Subquery Scan twotables (cost=100000000.00..200023000.53 rows=569553
width=203) (actual time=31590.97..31590.97 rows=0 loops=1)
-> Append (cost=100000000.00..200023000.53 rows=569553 width=203)
(actual time=12.13..30683.67 rows=569553 loops=1)
-> Subquery Scan *SELECT* 1 (cost=100000000.00..100021799.06
rows=540306 width=199) (actual time=12.12..28417.81 rows=540306
loops=1)
-> Seq Scan on file_92_1004
(cost=100000000.00..100021799.06 rows=540306 width=199) (actual
time=12.09..14946.47 rows=540306 loops=1)
-> Subquery Scan *SELECT* 2 (cost=100000000.00..100001201.47
rows=29247 width=203) (actual time=0.19..1525.18 rows=29247 loops=1)
-> Seq Scan on file_92_904
(cost=100000000.00..100001201.47 rows=29247 width=203) (actual
time=0.14..793.34 rows=29247 loops=1)
Total runtime: 31591.34 msec

EXPLAIN

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
o.*********@secomintl.com (Otto Blomqvist) writes:
I am obviously doing something wrong or using something the wrong way.


What PG version are you using? 7.3 and later can push the WHERE
condition down into the view, but older versions won't.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.