pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Bitmead <cbitmead@ozemail.com.au>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Beginner's headache of joins
Date: Thu, 01 Jul 1999 12:13:35 +1000
Message-ID: <377ACECF.39DB85D1@ozemail.com.au> (raw)
References: <99063010322700.00904@localhost.localdomain>

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



view thread (4+ messages)  latest in thread

Message-ID: <377ACECF.39DB85D1@ozemail.com.au>
Permalink:  ../377ACECF.39DB85D1@ozemail.com.au/
Also on:    postgresql.org/message-id/377ACECF.39DB85D1@ozemail.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: cbitmead@ozemail.com.au
  Subject: Re: [SQL] Beginner's headache of joins
  In-Reply-To: <377ACECF.39DB85D1@ozemail.com.au>
  References: <99063010322700.00904@localhost.localdomain> <377ACECF.39DB85D1@ozemail.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox