From: Chuck D. <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
Message-ID: <200702141312.23437.pgsql-performance@nullmx.com> (raw)
In-Reply-To: <11207.1171399908@sss.pgh.pa.us>
References: <200702131253.55486.pgsql-performance@nullmx.com>
<200702131417.32154.pgsql-performance@nullmx.com>
<11207.1171399908@sss.pgh.pa.us>
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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql-performance@nullmx.com
Subject: Re: JOIN to a VIEW makes a real slow query
In-Reply-To: <200702141312.23437.pgsql-performance@nullmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox