Re: JOIN to a VIEW makes a real slow query

On 2/13/07, Chuck D. <pgsql-performance(at)nullmx(dot)com> wrote:
> Hi folks,
>
> I don't know if this is an SQL or PERFORMANCE list problem but I wanted to
> check here first. I've seen this discussed on the list before but I'm still
> not sure of the solution. Maybe my query is just structured wrong.
>
> I recently visited an old project of mine that has a 'city', 'state,'
> and 'country' tables. The city data comes from multiple sources and totals
> about 3 million rows. I decided to split the city table up based on the
> source (world_city, us_city). This makes easier updating because the
> assigned feature id's from the two sources overlap in some cases making it
> impossible to update as a single merged table.
>
> However, I decided to create a view to behave like the old 'city' table. The
> view is just a simple:
>
> SELECT [columns]
> FROM world_city
> UNION
> SELECT [columns]
> FROM us_city
> ;
>
> Selecting from the view is very quick, but JOINing to the view is slow. About
> 65 seconds to select a city. It doesn't matter wether it is joined to one
> table or 6 like it is in my user_detail query - it is still slow. It has
> indexes on the city_id, state_id, country_id of each table in the view too.
> Everything has been 'VACUUM ANALYZE' ed.

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.

merlin