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: Wed, 14 Feb 2007 13:12:22 -0600
User-Agent: KMail/1.9.5
References: <200702131253.55486.pgsql-performance@nullmx.com>
	<200702131417.32154.pgsql-performance@nullmx.com>
	<11207.1171399908@sss.pgh.pa.us>
In-Reply-To: <11207.1171399908@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200702141312.23437.pgsql-performance@nullmx.com>


On Tuesday 13 February 2007 14:51, Tom Lane wrote:
> "Chuck D." <pgsql-performance@nullmx.com> writes:
> > It is still using that sequence scan on the view after the APPEND for t=
he
> > 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.
>
> 8.1 isn't bright enough for that.  Should work in 8.2 though.

>
> 			regards, tom lane

Upgraded to 8.2.3 in my spare time here - went from the packaged binary tha=
t=20
came with Ubuntu to compiling from source.  Haven't tuned it yet, but what =
do=20
you think about this join on the view?


cmi=3D# 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 =3D ci.city_id)
cmi-# ;
                                                               QUERY PLAN
=2D------------------------------------------------------------------------=
=2D--------------------------------------------------------------
 Nested Loop Left Join  (cost=3D0.00..17.76 rows=3D10614 width=3D486) (actu=
al=20
time=3D0.109..0.113 rows=3D1 loops=3D1)
   Join Filter: (mu.city_id =3D ci.city_id)
   ->  Seq Scan on m_user mu  (cost=3D0.00..1.01 rows=3D1 width=3D72) (actu=
al=20
time=3D0.015..0.017 rows=3D1 loops=3D1)
   ->  Append  (cost=3D0.00..16.72 rows=3D2 width=3D422) (actual time=3D0.0=
73..0.075=20
rows=3D1 loops=3D1)
         ->  Index Scan using pk_us_city on us_city  (cost=3D0.00..8.28 row=
s=3D1=20
width=3D222) (actual time=3D0.032..0.032 rows=3D0 loops=3D1)
               Index Cond: (mu.city_id =3D us_city.city_id)
         ->  Index Scan using world_city_pk on world_city  (cost=3D0.00..8.=
44=20
rows=3D1 width=3D422) (actual time=3D0.040..0.042 rows=3D1 loops=3D1)
               Index Cond: (mu.city_id =3D world_city.city_id)
 Total runtime: 0.359 ms
(9 rows)


=46rom 65 seconds down to less than 1 ms.  Pretty good huh?  Nice call Tom.=
 =20

Now I'll have to find some time to do the production server before this app=
=20
goes up.