Обсуждение: ...

I'm having some problems with query optimisation, using postgresql 7.2.

Basically, I'm hacking on porting bugzilla to pgsql, and I'm trying 
various queries on a very small subset of the schema:

CREATE TABLE bugs ( bug_id integer NOT NULL auto_increment, assigned_to integer NOT NULL, reporter integer NOT NULL,
PRIMARYKEY (bug_id)
 
);
CREATE INDEX bugs_assigned_to_idx ON bugs(assigned_to);
CREATE INDEX bugs_reporter_idx ON bugs(reporter);

CREATE TABLE cc ( bug_id integer NOT NULL, who integer NOT NULL
);
CREATE INDEX cc_who_idx ON cc(who);
CREATE UNIQUE INDEX cc_bug_id_who_idx ON cc(bug_id,who);

I've used a perl script to insert 25000 users, 10000 bugs, and 30000 cc 
entries randomly, and I then ran ANALYZE on the database. The script just 
uses rand, (plus some hashes to check that uniqueness still hold) so the 
data should be fairly evenly distributed.

Now I want to search for all bugs which user #86 is ccd on:

bugs=> explain analyze SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON 
bugs.bug_id = cc.bug_id WHERE cc.who=86;             
NOTICE:  QUERY PLAN:

Merge Join  (cost=0.00..1817.34 rows=10030 width=12) (actual 
time=1516.75..2951.11 rows=1 loops=1) ->  Index Scan using bugs_pkey on bugs  (cost=0.00..201.30 rows=10030 
width=4) (actual time=0.33..135.67 rows=10000 loops=1) ->  Index Scan using cc_bug_id_who_idx on cc
(cost=0.00..1065.97
 
rows=30000 width=8) (actual time=0.37..2522.77 rows=30000 loops=1)
Total runtime: 2951.37 msec

EXPLAIN
bugs=> 

However, if I disable merge joins:

bugs=> set enable_mergejoin=0;
SET VARIABLE
bugs=> explain analyze SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON 
bugs.bug_id = cc.bug_id WHERE cc.who=86;
NOTICE:  QUERY PLAN:

Hash Join  (cost=1212.86..3288.98 rows=10030 width=12) (actual 
time=1024.50..1237.59 rows=1 loops=1) ->  Seq Scan on bugs  (cost=0.00..159.30 rows=10030 width=4) (actual 
time=0.16..70.25 rows=10000 loops=1) ->  Hash  (cost=463.00..463.00 rows=30000 width=8) (actual 
time=284.51..284.51 rows=0 loops=1)       ->  Seq Scan on cc  (cost=0.00..463.00 rows=30000 width=8) (actual 
time=0.13..159.54 rows=30000 loops=1)
Total runtime: 1237.78 msec

EXPLAIN
bugs=> 

Then the time taken more than halves - 2951ms to 1237ms.  Is this a bug in 
the optimiser?

Also, in this case it would be better to use an inner join rather than a
left join. Since there is a condition on cc.who which won't match NULL
values from the cc table, an inner join should give the same results,
shouldn't it? Using an inner join makes the query take 0.98msec, so it
would be good if postgres could do that optimisation automatically,
assuming that it is valid. (This particular query is generated from a perl
script, and some cases do need the left join. It should probably be fixed 
on that side, but it would be nice if pg could do it automatically.)

Thanks,


Bradley

Bradley Baetz <bbaetz@student.usyd.edu.au> writes:
> I'm having some problems with query optimisation, using postgresql 7.2.

It looks like on your hardware, seqscans are much cheaper relative to
indexscans than the optimizer is expecting.  Note the ratios of cost
estimates to actual runtimes.  You might care to experiment with the
optimizer parameters, such as random_page_cost, to see if you can get
closer to the actual behavior of your configuration.

> Also, in this case it would be better to use an inner join rather than a
> left join. Since there is a condition on cc.who which won't match NULL
> values from the cc table, an inner join should give the same results,
> shouldn't it?

It's not so much that you want an inner join as that you want to be able
to figure out that the cc.who=86 condition could be applied before
joining rather than after.  I have not thought hard about how the
optimizer could determine whether this is a safe transformation.  In
general it's obviously not safe when dealing with an outer join --- but
maybe in some cases we could allow it.  Can anyone propose a rule?
        regards, tom lane

PS: this discussion would be better suited for pgsql-hackers, I think.