Skip site navigation (1) Skip section navigation (2)

LEFT JOIN optimization

From: Ksenia Marasanova <ksenia(dot)marasanova(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: LEFT JOIN optimization
Date: 2005-09-11 17:12:58
Message-ID: 130df19305091110125a2d174f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi list,

I don't have much experience with Postgres optimization, somehow I was
happily avoiding anything more difficult than simple select statement,
and it was working all right.

Now LEFT JOIN must be used, and I am not happy with the performance:
It takes about 5 seconds to run very simple LEFT JOIN query on a table
"user_" with ~ 13.000 records left joined to table "church" with ~ 300
records on Powerbook PPC 1.67 GHz with 1.5 GB ram.
Is it normal?

Some details:

test=# explain select * from user_ left join church on user_.church_id
= church.id;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Hash Left Join  (cost=6.44..7626.69 rows=12763 width=325)
   Hash Cond: ("outer".church_id = "inner".id)
   ->  Seq Scan on user_  (cost=0.00..7430.63 rows=12763 width=245)
   ->  Hash  (cost=5.75..5.75 rows=275 width=80)
         ->  Seq Scan on church  (cost=0.00..5.75 rows=275 width=80)
(5 rows)


From what I understand, it doesn't use foreign key index on user_
table. So I tried:

mydb=# set enable_seqscan='false';
SET
mydb=# explain select * from user_ left join church on user_.church_id
= church.id;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Merge Right Join  (cost=0.00..44675.77 rows=12763 width=325)
   Merge Cond: ("outer".id = "inner".church_id)
   ->  Index Scan using chirch_pkey on church  (cost=0.00..17.02
rows=275 width=80)
   ->  Index Scan using user__church_id on user_  (cost=0.00..44500.34
rows=12763 width=245)
(4 rows)


It's my first time reading Query plans, but from wat I understand, it
doesn't make the query faster..

Any tips are greatly appreciated.
-- 
Ksenia

Responses

pgsql-performance by date

Next:From: Stephen FrostDate: 2005-09-11 21:00:36
Subject: Re: LEFT JOIN optimization
Previous:From: John A MeinelDate: 2005-09-11 12:35:53
Subject: Re: shared buffers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group