pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
different query plan for same select
3+ messages / 2 participants
[nested] [flat]

* different query plan for same select
@ 2003-07-23 14:28 Jörg Schulz <jschulz@sgbs.de>
  2003-07-23 15:21 ` Re: different query plan for same select Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Jörg Schulz @ 2003-07-23 14:28 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: different query plan for same select
  2003-07-23 14:28 different query plan for same select Jörg Schulz <jschulz@sgbs.de>
@ 2003-07-23 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-24 06:06   ` Re: different query plan for same select Jörg Schulz <jschulz@sgbs.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2003-07-23 15:21 UTC (permalink / raw)
  To: Jörg Schulz <jschulz@sgbs.de>; +Cc: pgsql-performance

=?iso-8859-1?q?J=F6rg=20Schulz?= <jschulz@sgbs.de> writes:
> I'm not very familar with the output of the explain command but can you
> tell me why I get two different query plans?

Judging from the suspiciously round numbers in the cost estimates,
you've never done a VACUUM ANALYZE on any of these tables.  Try that and
then see what plans you get...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: different query plan for same select
  2003-07-23 14:28 different query plan for same select Jörg Schulz <jschulz@sgbs.de>
  2003-07-23 15:21 ` Re: different query plan for same select Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-24 06:06   ` Jörg Schulz <jschulz@sgbs.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Jörg Schulz @ 2003-07-24 06:06 UTC (permalink / raw)
  To: pgsql-performance

> > I'm not very familar with the output of the explain command but can you
> > tell me why I get two different query plans?
>
> Judging from the suspiciously round numbers in the cost estimates,
> you've never done a VACUUM ANALYZE on any of these tables.  Try that and
> then see what plans you get...

Oops.. You were right!

Thank you anyway.

Jörg



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-07-24 06:06 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 14:28 different query plan for same select Jörg Schulz <jschulz@sgbs.de>
2003-07-23 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-24 06:06   ` Jörg Schulz <jschulz@sgbs.de>

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