Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 41EDA4761A5 for ; Mon, 25 Feb 2002 11:48:21 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1PGmMS21642; Mon, 25 Feb 2002 11:48:22 -0500 (EST) To: Bradley Baetz Cc: pgsql-sql@postgresql.org Subject: Re: In-reply-to: References: Comments: In-reply-to Bradley Baetz message dated "Sat, 23 Feb 2002 21:21:07 +1100" Date: Mon, 25 Feb 2002 11:48:22 -0500 Message-ID: <21639.1014655702@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/358 X-Sequence-Number: 6780 Bradley Baetz 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.