Re: Join efficiency

On Wed, Sep 02, 2009 at 10:11:24PM +0900, tanjunhua wrote:
> thanks for your response.
>
> >Maybe if you could describe what you want to do in English then the
> >query would make a bit more sense.
> I just want those records as the below rule:
> 1. the record of which uid is 2, status is more than 20, bpassword is 0
> and realdelflag is 0 in tab_main;
> 1.1 the record of which kind is 1 in those that filtered through step1;
> 1.2 the record of which kind is 0 in those that filtered through step1;
> 1.2.1 the record of which delflag doesn't equal 0 in those filtered
> through step1.2;
> 1.2.2 the record of which uid equal 2, printauth equal 2 or 3 and
> bprtpermit equal 0 in tab_user left join those filtered through step1.2;
> 1.2.2.1 the record of which mode equal to 0 or 1 in tab_property and left
> join those filtered through step1.2.2 using id;

That's not a very "english" explanation. That's just a translation of
what the code does, and I can do that easily enough myself. What you're
missing is what the query "means" and the intuition as to how to go
about understanding what all that really means.

I'm guessing there's a clever combination of outer joins that would make
this go fast, but I've tried to do the translation but it's all a bit
complicated to do in my head. I think it's something like:

SELECT COUNT(DISTINCT t1.id)
FROM tab_main t1
LEFT JOIN (SELECT TRUE AS userok FROM tab_user WHERE uid = 2 AND printauth IN (2,3) AND bprtpermit = 0 GROUP BY 1) t2 ON TRUE,
LEFT JOIN tab_property t3 ON t1.id = t3.id AND t3.mode IN (0,1)
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.userok AND t3.id IS NOT NULL))));

but I'm not sure how much I'd trust that without some testing.

> It is my first time to use database in practise, could you give me more
> detail? such as how to decision the WHERE clause complication?
> how to make the best choice by analyze result? Would you supply some
> documents about postgresql performance?

There are lots of guides around on the internet; google is your friend!
Other than trying to rewrite your queries in different ways I'm not sure
what to suggest, it'll give you experience which is the important thing.

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