pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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