Re: Join efficiency

On Wed, Sep 02, 2009 at 02:31:46PM +0900, tanjunhua wrote:
> I
> have the trouble that it cost me a lot of time when execute the select
> syntax. the following is the select syntax and analyze result.

> EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM
> tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status
> >= 21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR
> (t1.kind = 0 AND (t1.delflag <> 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR
> t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0
> OR t3.mode = 1))))))) subt0;

That WHERE clause is far too complicated to allow PG's optimizer to have
a chance. The "Nested Loop" running over sequential scans is a sign
that things aren't going to work out well.

OR clauses are the awkward one, as you've got one at the top of your
WHERE clause it's going to force PG to do slow things. It looks
somewhat strange as well, do you really want to join *every* row in
"tab_main" to *every* row in "tab_user" when "tab_main.kind" doesn't
equal zero?

Maybe if you could describe what you want to do in English then the
query would make a bit more sense.

--
Sam http://samason.me.uk/