Re: Join efficiency

I'm sorry for my mistake, the postgre version is:

test_db=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

----- Original Message -----
From: "tanjunhua" <tanjh(at)riso(dot)co(dot)jp>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, September 02, 2009 2:31 PM
Subject: [GENERAL] Join efficiency

> Hello, everybody.
> In my project, I have a select syntax to get record summary between three
> tables. one of them is tab_main consist of 46 columns(with 27797
> records), another is tab_user consist of 32 columns(with 3 records) and
> the last one is tab_property consist of 117 columns(with 30541 records). 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.
>
> table structure:
> tab_main(id, uid, status, bpassword, realdelflag, delflag, kind, ...)
> tab_user(uid, printauth, bprtpermit, ...)
> tab_property(id, mode, ...)
>
> 1. select syntax:
> 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;
>
> 2. analyze result:
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=19129.93..19129.94 rows=1 width=4) (actual
> time=10661.656..10661.658 rows=1 loops=1)
> -> Unique (cost=18672.11..19129.92 rows=1 width=4) (actual
> time=8288.446..10661.586 rows=5 loops=1)
> -> Sort (cost=18672.11..18901.01 rows=91562 width=4) (actual
> time=8288.440..9532.507 rows=458115 loops=1)
> Sort Key: t1.id
> -> Nested Loop (cost=1550.00..10341.45 rows=91562 width=4)
> (actual time=5.002..4724.436 rows=458115 loops=1)
> Join Filter: (("inner".jobkind = 1) OR
> (("inner".jobkind = 0) AND (("inner".delflag <> 0) OR (("inner".uid = 2)
> AND (("inner".printright = 2) OR ("inner".printright = 3)) AND
> ("inner".print_permitcolorprint = 0) AND ("outer".id = "inner".id) AND
> (("outer".colormode = 0) OR ("outer".colormode = 1))))))
> -> Seq Scan on job_p t3 (cost=0.00..4668.41
> rows=30541 width=8) (actual time=0.023..170.619 rows=30541 loops=1)
> -> Materialize (cost=1550.00..1550.03 rows=3
> width=24) (actual time=0.002..0.055 rows=21 loops=30541)
> -> Nested Loop (cost=0.00..1550.00 rows=3
> width=24) (actual time=4.949..149.081 rows=21 loops=1)
> -> Seq Scan on job_ctl t1
> (cost=0.00..1548.94 rows=1 width=12) (actual time=4.906..148.794 rows=7
> loops=1)
> Filter: ((uid = 2) AND (jobsts >=
> 21) AND (pinflag = 0) AND (realdelflag = 0))
> -> Seq Scan on users t2 (cost=0.00..1.03
> rows=3 width=12) (actual time=0.007..0.016 rows=3 loops=7)
> Total runtime: 10696.630 ms
> (13 rows)
>
> could anyone explain the result of analyze and give me some idea to speed
> up the select? looking forward your response.
> best wishes.
>
> winsea