[snip] Rory Campbell-Lange wrote:
SELECT DISTINCT
b.n_id as id,
b.n_type,
CASE b.n_type WHEN 0 THEN 'personal'
WHEN 1 THEN 'private'
WHEN 2 THEN 'blog'
ELSE 'public'
END as type,
b.t_name as title
FROM
boards b, people p, objects o
WHERE
b.b_hidden = 'f' AND ( b.n_type = 3 OR b.n_creator = 71
OR
( b.n_id = o.n_board_id AND o.n_creator = 71
AND
o.n_joined > 0
)
)
ORDER BY b.n_type, b.n_id ASC, b.t_name;
You don't seem to be joining the people table to the boards or objects table. In fact the people table is never referenced anywhere but the FROM clause. This might be why it is not running so well. People seems to be referenced by both boards and objects, so does there need to be a join between all 3 tables?
[snip]
"$1" FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE
[snip]
"$1" FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE
It is this "missing" join which seems to cause 158254 rows to appear in this nested loop, when PG only reckons it is going to see 2738 rows
-> Nested Loop (cost=3442.79..3520.93 rows=2738 width=18) (actual time=0.244..1052.180 rows=158254 loops=1)
I cant remember what the correct term is, but basically all rows from the seq scan combines with all rows from the materialize, which is what is causing the 150,000 odd rows to appear (is that called a cross product anyone?)
i.e
67 rows from the seq x 2362 rows from the materialize = 158254 output rows for the nested loop to chug through.
-> Seq Scan on people p (cost=0.00..2.67 rows=67 width=0) (actual time=0.012..0.709 rows=67 loops=1) -> Materialize (cost=3442.79..3443.20 rows=41 width=18) (actual time=0.005..6.528 rows=2362 loops=67)
I reckon fix the unconstrained join and your query might run a little faster.
>> -> Nested Loop (cost=3.27..3442.79 rows=41 width=18) (actual time=0.216..273.709 rows=2362 loops=1)
The estimate for this nested loop seems a little off, note the guesstimate in the first set of brackets of 41, and compare with the actual result in the second set of 2362. Have you vacuum analyzed recently?
HTH
Nick
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
