Re: Join efficiency - Mailing list pgsql-general

From tanjunhua
Subject Re: Join efficiency
Date
Msg-id 181701ca2bce$dfd62090$aa1c10ac@RKC.local
Whole thread Raw
In response to Join efficiency  ("tanjunhua" <tanjh@riso.co.jp>)
Responses Re: Join efficiency
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



pgsql-general by date:

Previous
From: Inigo Barandiaran
Date:
Subject: Re: PosgreSQL Service does not Start In Vista
Next
From: "Massa, Harald Armin"
Date:
Subject: Re: creating array of integer[] out of query - how?