Re: Very slow query

[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