On 2/13/07, Chuck D. <pgsql-performance@xxxxxxxxxx> 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
