From: "Chuck D." <pgsql-performance@nullmx.com>
Reply-To: pgsql-performance@nullmx.com
To: pgsql-performance@postgresql.org
Subject: Re: JOIN to a VIEW makes a real slow query
Date: Tue, 13 Feb 2007 14:17:31 -0600
User-Agent: KMail/1.9.5
References: <200702131253.55486.pgsql-performance@nullmx.com>
	<b42b73150702131116ic89dab3q38c103b7c1930384@mail.gmail.com>
In-Reply-To: <b42b73150702131116ic89dab3q38c103b7c1930384@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200702131417.32154.pgsql-performance@nullmx.com>

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)