From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Bertheau <twanger@bluetwanger.de>
Cc: postgres sql list <pgsql-sql@postgresql.org>
Subject: Re: strange query execution times
Date: Tue, 02 Oct 2001 11:49:49 -0400
Message-ID: <9106.1002037789@sss.pgh.pa.us> (raw)
In-Reply-To: <1001958754.2485.31.camel@entwicklung01.cenes.de>
References: <1001958754.2485.31.camel@entwicklung01.cenes.de>
Markus Bertheau <twanger@bluetwanger.de> writes:
> r_kunden_anbieter describes the relationship between customers and
> suppliers. there are five status, 0 to 4 in attribute beziehung. both
> queries return the same results. they select all customers which have a
> certain relationship (beziehung = 3) to a given supplier.
> personen has 484 rows, r_kunden_anbieter 327.
How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"?
The issue appears to be that the planner estimates one matching row
in the one case and two matching rows in the second. Given the estimate
of one row, it decides to go for the low-overhead nested loop plan.
I am guessing that there are really considerably more than two matching
rows, and so the nested loop plan loses badly compared to the mergejoin,
which takes longer to set up but is better able to cope with many rows.
FWIW, 7.2 has better statistics and should be better able to pick the
right plan in this context ...
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, twanger@bluetwanger.de
Subject: Re: strange query execution times
In-Reply-To: <9106.1002037789@sss.pgh.pa.us>
* 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