different query plan for same select

From: Jörg Schulz <jschulz(at)sgbs(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: different query plan for same select
Date: 2003-07-23 14:28:54
Message-ID: 200307231628.54295.jschulz@sgbs.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have these two tables with the same data on two different
machines (SuSE 8.2 and Gentoo both with 7.3.2):

schulz=> \d rechnung
Table "jschulz.rechnung"
Column | Type | Modifiers
----------------+----------+-----------
system | smallint | not null
jahr | smallint | not null
monat | smallint | not null
rechnungsnr | integer | not null
rechnungsdatum | date | not null
kundennr | integer | not null
seiten | smallint | not null
formularnr | smallint |
text | text | not null
Indexes: rechnung_pkey primary key btree (system, jahr, rechnungsnr),
rechnung_kundennr btree (kundennr),
rechnung_rechnungsdatum btree (rechnungsdatum),
rechnung_rechnungsnr btree (rechnungsnr)

schulz=> \d rechnung_zusatz
Table "jschulz.rechnung_zusatz"
Column | Type | Modifiers
-------------+----------+-----------
system | smallint | not null
jahr | smallint | not null
rechnungsnr | integer | not null
objektnr | integer |
Indexes: rechnung_zusatz_uniq_objektnr unique btree (system, jahr,
rechnungsnr, objektnr),
rechnung_zusatz_objektnr btree (objektnr)
Foreign Key constraints: $1 FOREIGN KEY (system, jahr, rechnungsnr) REFERENCES
rechnung(system, jahr, rechnungsnr) ON UPDATE NO ACTION ON DELETE NO ACTION

schulz=>

On the SuSE machine an explain gives the following:

schulz=> explain select system, jahr, rechnungsnr from (rechnung natural left
join rechnung_zusatz) where objektnr=1;
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=0.00..25.04 rows=1000 width=20)
Hash Cond: ("outer".rechnungsnr = "inner".rechnungsnr)
Join Filter: (("outer".system = "inner".system) AND ("outer".jahr =
"inner".jahr))
Filter: ("inner".objektnr = 1)
-> Seq Scan on rechnung (cost=0.00..20.00 rows=1000 width=8)
-> Hash (cost=0.00..0.00 rows=1 width=12)
-> Seq Scan on rechnung_zusatz (cost=0.00..0.00 rows=1 width=12)
(7 rows)

schulz=>

On the Gentoo machine the same explain gives:

schulz=> explain select system, jahr, rechnungsnr from (rechnung natural left
join rechnung_zusatz) where objektnr=1;
QUERY PLAN
---------------------------------------------------------------------------------
Merge Join (cost=0.00..109.00 rows=1000 width=20)
Merge Cond: (("outer".system = "inner".system) AND ("outer".jahr =
"inner".jahr) AND ("outer".rechnungsnr = "inner".rechnungsnr))
Filter: ("inner".objektnr = 1)
-> Index Scan using rechnung_pkey on rechnung (cost=0.00..52.00 rows=1000
width=8)
-> Index Scan using rechnung_zusatz_uni_objektnr on rechnung_zusatz
(cost=0.00..52.00 rows=1000 width=12)
(5 Zeilen)

schulz=>

The select on the SuSE machine finishes in about 3 seconds and on the
Gentoo machine it doesn't seem to come to an end at all. Each table has
about 80.000 rows.

I'm not very familar with the output of the explain command but can you
tell me why I get two different query plans?

Jörg

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephan Szabo 2003-07-23 14:49:12 Re: Performance hit of foreign key constraints?
Previous Message Richard Huxton 2003-07-23 14:13:14 Re: factoring problem with view in 7.3.3