Re: Join efficiency - Mailing list pgsql-general

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;

such as the following data, the expect result is 1 record and detail
information is:
  id   | uid | status| bpassword| realdelflag| delflag| kind
-------+-----+--------+---------+-------------+---------+---------
 39731 |   2 |     21 |       0 |           0 |       0 |       1

tab_main:
  id   | uid | status| bpassword| realdelflag| delflag| kind
-------+-----+--------+---------+-------------+---------+---------
 39752 |   1 |      0 |       0 |           0 |       0 |       2
 39751 |   1 |     21 |       0 |           0 |      -1 |       2
 39750 |   2 |      0 |       1 |           0 |       0 |       2
 39749 |   2 |     21 |       1 |           0 |      -1 |       2
 39748 |   2 |      0 |       1 |           0 |       0 |       2
 39731 |   2 |     21 |       0 |           0 |       0 |       1
 39728 |   2 |      1 |       1 |           0 |       0 |       1
 39727 |   2 |      1 |       0 |           0 |       0 |       1
 39710 |   0 |      0 |       0 |           0 |       0 |       1
 39709 |   2 |      0 |       1 |           0 |       0 |       1
 39681 |   0 |      0 |       0 |           0 |      -1 |       0
  4333 |   0 |      0 |       0 |           0 |      -1 |       0

tab_user:
 uid | printauth| bprtpermit
-----+------------+------------------------
   1 |          1 |                      1
   2 |          2 |                      0
   3 |          1 |                      1

tab_property:
  id   | mode
-------+-----------
 39731 |         1
 39728 |         4
 39727 |         4
 39710 |         1
 39709 |         0

> 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 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?

bese wishes.

winsea