On Tuesday 13 February 2007 13:16, Merlin Moncure wrote:
>
> use 'union all' instead of union. union without all has an implied
> sort and duplicate removal step that has to be resolved, materializing
> the view, before you can join to it.
>
Thanks for that Merlin, I forgot about using ALL. That does eliminate the
UNIQUE, SORT and SORT lines from the EXPLAIN query. It also brings the query
time down from a whopping 65 seconds to 11 seconds. The two tables contain
unique rows already so ALL would be required.
It is still using that sequence scan on the view after the APPEND for the
us_city and world_city table. Any reason why the view won't use the indexes
when it is JOINed to another table but it will when the view is queried
without a JOIN? I should have mentioned this is v8.1.4.
Also, does anyone know why this line:
Join Filter: ("outer".city_id = "inner"."?column1?")
... contains "?column1?" instead of the actual column name?
This is the result after UNION ALL on the view
cmi=# explain analyze
cmi-# select user_id, username, city_name
cmi-# FROM m_user AS mu
cmi-# LEFT JOIN geo.city_vw AS ci ON (mu.city_id = ci.city_id)
cmi-# ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..121523.88 rows=10618 width=55) (actual
time=2392.376..11061.117 rows=1 loops=1)
Join Filter: ("outer".city_id = "inner"."?column1?")
-> Seq Scan on m_user mu (cost=0.00..1.01 rows=1 width=27) (actual
time=0.025..0.028 rows=1 loops=1)
-> Append (cost=0.00..73741.94 rows=2123597 width=62) (actual
time=16.120..9644.315 rows=2122712 loops=1)
-> Seq Scan on us_city (cost=0.00..4873.09 rows=169409 width=62)
(actual time=16.119..899.802 rows=169398 loops=1)
-> Seq Scan on world_city (cost=0.00..47632.88 rows=1954188
width=61) (actual time=10.585..6949.946 rows=1953314 loops=1)
Total runtime: 11061.441 ms
(7 rows)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
