Re: JOIN to a VIEW makes a real slow query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tuesday 13 February 2007 14:51, Tom Lane wrote:
> "Chuck D." <pgsql-performance@xxxxxxxxxx> writes:
> > 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.
>
> 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 that 
came with Ubuntu to compiling from source.  Haven't tuned it yet, but what do 
you think about this join 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..17.76 rows=10614 width=486) (actual 
time=0.109..0.113 rows=1 loops=1)
   Join Filter: (mu.city_id = ci.city_id)
   ->  Seq Scan on m_user mu  (cost=0.00..1.01 rows=1 width=72) (actual 
time=0.015..0.017 rows=1 loops=1)
   ->  Append  (cost=0.00..16.72 rows=2 width=422) (actual time=0.073..0.075 
rows=1 loops=1)
         ->  Index Scan using pk_us_city on us_city  (cost=0.00..8.28 rows=1 
width=222) (actual time=0.032..0.032 rows=0 loops=1)
               Index Cond: (mu.city_id = us_city.city_id)
         ->  Index Scan using world_city_pk on world_city  (cost=0.00..8.44 
rows=1 width=422) (actual time=0.040..0.042 rows=1 loops=1)
               Index Cond: (mu.city_id = world_city.city_id)
 Total runtime: 0.359 ms
(9 rows)


From 65 seconds down to less than 1 ms.  Pretty good huh?  Nice call Tom.  

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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux