I have indices on both tables for route_id and year. I neglected to mention that I am running 6.5.2 and have passed -F to the backend. I'm starting to think that postgres is getting caught in a non-terminating loop. Any other suggestions? Tim amy cheng wrote: > seems that you should have index both for tenstop.route_id and > history.route_id . From the index name, it not clear if you have > or not. No matter what, add indeces!!! > > >From: "Timothy H. Keitt" <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: [GENERAL] query seems too slow > >Date: Wed, 03 Nov 1999 11:17:52 -0800 > > > >I'm trying to join two tables, one with > 3M rows and another with a few > >thousand rows, and store the output in a new table. "EXPLAIN" gives the > >following output: > > > >keitt=> explain select tenstop.* into tmp from tenstop, history where > >tenstop.route_id = history.route_id and tenstop.year = history.year and > >history.run_type = 1; > >NOTICE: QUERY PLAN: > > > >Nested Loop (cost=6.15 rows=1 width=56) > > -> Index Scan using history_run_type_index on history (cost=2.05 > >rows=2 width=8) > > -> Index Scan using tenstop_year_index on tenstop (cost=2.05 > >rows=3316684 width=48) > > > >EXPLAIN > >keitt=> > > > >I fired this off yesterday and it was still not finished this morning > >after 12+ hours. Should I be suprised? How long should a query like > >this take? (This is on a PII 450 w/ 256M ram.) > > > >Tim > > > >-- > >Timothy H. Keitt > >National Center for Ecological Analysis and Synthesis > >735 State Street, Suite 300, Santa Barbara, CA 93101 > >Phone: 805-892-2519, FAX: 805-892-2510 > >http://www.nceas.ucsb.edu/~keitt/ > > > > > > > > > >************ > > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/ ************
