Received: from localhost.localdomain (h246.ozemail2.ozemail.com.au [203.108.14.246]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA51478 for ; Wed, 30 Jun 1999 22:13:39 -0400 (EDT) (envelope-from cbitmead@ozemail.com.au) Received: from ozemail.com.au (localhost [127.0.0.1]) by localhost.localdomain (8.9.3/8.9.3) with ESMTP id MAA02556 for ; Thu, 1 Jul 1999 12:13:35 +1000 Sender: chris@localhost.localdomain Message-ID: <377ACECF.39DB85D1@ozemail.com.au> Date: Thu, 01 Jul 1999 12:13:35 +1000 From: Chris Bitmead X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.6 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Beginner's headache of joins References: <99063010322700.00904@localhost.localdomain> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Telling us the query you're doing might help. webmaster wrote: > > Hi all, > I'm trying to deal with joins the first time and it looks like I'm stupid > enough to solve the problem without your help :) > > My main problem is when I'm performing SELECT on 3 tables - all the keys are > indexed - Postgresql is working for so long time that I don't thing everything > is ok. My last try ended with disk full of files like HJ999... at the database > directory - so I know the postgresql is performing something called hash > join... I don't know exactly if it is ok or not. IMHO my select job is not so > complicated and tables are not so big to end with 300MB of temporary files (and > it didn't finished at all), so something IS wrong... > > OK, I want to solve it myself... I was studying the docs so I know something > more about it and I started experimenting with easier join of two tables. But > I'm not sure what does mean following result of EXPLAIN SELECT: > > explain select catnum.catnum, kat.ident, kat.nazev where > catnum.catnum=kat.ident; > > resulting as: > > HASH JOIN ... > -> SEQ SCAN ON catnum ... > -> HASH ... > -> SEQ SCAN ON kat... > > So, I can see that it's performing two scans without using indexes. Why? both > catnum.catnum and kat.ident are indexed (default index type)... I've expected > one index scan, but there is not. What's wrong? I can't find the description of > such problem at docs... I've tried VACUUMing both the tables but result > is the same. My postgresql is 6.4.2. Maybe i'm using wrong index type? Maybe > it's ok and I'm not understanding the whole join thing? > > Anyway thanks for any comments and sorry for my school English... > > -- > Michal Samek, Tony distribuce s.r.o. > webmaster@tony.cz (++420659/321350) > ICQ: 38