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" <keitt@nceas.ucsb.edu>
> >To: pgsql-general@postgreSQL.org
> >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/