Re: JOIN to a VIEW makes a real slow query

On Tuesday 13 February 2007 14:51, Tom Lane wrote:
> "Chuck D." <pgsql-performance(at)nullmx(dot)com> 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.