pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBeginner's headache of joins
4+ messages / 3 participants
[nested] [flat]
* Beginner's headache of joins
@ 1999-06-30 08:10 webmaster <webmaster@tony.cz>
0 siblings, 2 replies; 4+ messages in thread
From: webmaster @ 1999-06-30 08:10 UTC (permalink / raw)
To: pgsql-sql
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: 38607210
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Beginner's headache of joins
@ 1999-06-30 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: webmaster <webmaster@tony.cz>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-06-30 14:43 UTC (permalink / raw)
To: webmaster@tony.cz; +Cc: pgsql-sql
webmaster <webmaster@tony.cz> writes:
> 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?
Looks like a perfectly reasonable plan to me. The nice thing about a
hash join is that it doesn't need to examine the tables in sorted order,
so there's no need for the expense of an index scan. The system tries
to estimate whether this will be cheaper than a merge join (which does
need to scan the tables in sorted order), and evidently it thinks so.
You haven't given us nearly enough info to tell whether that's a good
decision or not, however. How big are the tables, and what are the data
types of the columns you're joining on? It would help also to see the
*full* output from EXPLAIN, including all the numeric values.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Beginner's headache of joins
@ 1999-07-01 02:13 Chris Bitmead <cbitmead@ozemail.com.au>
parent: webmaster <webmaster@tony.cz>
1 sibling, 0 replies; 4+ messages in thread
From: Chris Bitmead @ 1999-07-01 02:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Beginner's headache of joins
@ 1999-07-02 11:25 webmaster <webmaster@tony.cz>
0 siblings, 0 replies; 4+ messages in thread
From: webmaster @ 1999-07-02 11:25 UTC (permalink / raw)
To: pgsql-sql
On Wed, 30 Jun 1999, you wrote:
>webmaster <webmaster@tony.cz> writes:
>> 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?
>
>Looks like a perfectly reasonable plan to me. The nice thing about a
>hash join is that it doesn't need to examine the tables in sorted order,
>so there's no need for the expense of an index scan. The system tries
>to estimate whether this will be cheaper than a merge join (which does
>need to scan the tables in sorted order), and evidently it thinks so.
>
>You haven't given us nearly enough info to tell whether that's a good
>decision or not, however. How big are the tables, and what are the data
>types of the columns you're joining on? It would help also to see the
>*full* output from EXPLAIN, including all the numeric values.
>
> regards, tom lane
I'm a bit surprised that there is some better way to solve such querry than
using index scan - I though it should be done by seq. scanning first table and
for each rec. make one index scan to find if there is "linked" record at second
table... OK, it's something far away my dbase experiences :) Where I can find
more information about HASH JOIN algorithm? And why it's performing so slow
(compared to similar job using old good .dbf tables) ?
After my post to this mail. list I read here discussion about joins and
something like LINK field type. I think it was discussion about my problem (or
my point of view) too. Maybe sometimes it would be better to have some
control of querry optimizer? Maybe I'm out of SQL philosophy, I don't know...
Anyway, here are some details about my tables. The first is about 60 000 recs,
second is about 80 000 recs, and there is about 8000 matching records -
records which have the same value for id keys (varchar(20) and varchar(13)).
I'm just trying to link the information from some different source than
mine with my primary catalogue of music titles. OK, I believe you that it was
correct decision of the optimizer, if you say it. Evidently I should study more
the relational database concepts... Could somebody point me to some docs about
it?
--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-07-02 11:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-30 08:10 Beginner's headache of joins webmaster <webmaster@tony.cz>
1999-06-30 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-01 02:13 ` Chris Bitmead <cbitmead@ozemail.com.au>
1999-07-02 11:25 Re: [SQL] Beginner's headache of joins webmaster <webmaster@tony.cz>
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