From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bradley Baetz <bbaetz@student.usyd.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re:
Date: Mon, 25 Feb 2002 11:48:22 -0500
Message-ID: <21639.1014655702@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.44.0202232045320.2350-100000@tomato.home>
References: <Pine.LNX.4.44.0202232045320.2350-100000@tomato.home>
Bradley Baetz <bbaetz@student.usyd.edu.au> writes:
> I'm having some problems with query optimisation, using postgresql 7.2.
It looks like on your hardware, seqscans are much cheaper relative to
indexscans than the optimizer is expecting. Note the ratios of cost
estimates to actual runtimes. You might care to experiment with the
optimizer parameters, such as random_page_cost, to see if you can get
closer to the actual behavior of your configuration.
> Also, in this case it would be better to use an inner join rather than a
> left join. Since there is a condition on cc.who which won't match NULL
> values from the cc table, an inner join should give the same results,
> shouldn't it?
It's not so much that you want an inner join as that you want to be able
to figure out that the cc.who=86 condition could be applied before
joining rather than after. I have not thought hard about how the
optimizer could determine whether this is a safe transformation. In
general it's obviously not safe when dealing with an outer join --- but
maybe in some cases we could allow it. Can anyone propose a rule?
regards, tom lane
PS: this discussion would be better suited for pgsql-hackers, I think.
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, bbaetz@student.usyd.edu.au
Subject: Re:
In-Reply-To: <21639.1014655702@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