Re: BUG #5885: Strange rows estimation for left join

Hi.

Test case look like:

create table "references" ( attr_id integer, reference integer,
object_id integer );
insert into "references" select *100**(random()),
*100000**(random()^*10*), *1000000**(random()) from
generate_series(*1*,*10000000*);
create index xif01references on "references" ( reference, attr_id );
create index xif02references on "references" ( object_id, attr_id, reference );

analyze "references";

explain select * from "references" rs left join "references" vm on
vm.reference = rs.reference and vm.attr_id = *10* where rs.object_id =
*1000*;

explain analyze select * from "references" rs left join "references"
vm on vm.reference = rs.reference and vm.attr_id = *10* where
rs.object_id = *1000*;

On my system (8.4.4) it producing next results:

postgres=# explain select * from "references" rs left join
"references" vm on vm.reference = rs.reference and vm.attr_id = 10
where rs.object_id = 1000;

QUERY PLAN
-----------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..7.53 rows=107283 width=24)

-> Index Scan using xif02references on "references" rs
(cost=0.00..0.58 rows=11 width=12)
Index Cond: (object_id = 1000)
-> Index Scan using xif01references on "references" vm
(cost=0.00..0.53 rows=8 width=12)

Index Cond: ((vm.reference = rs.reference) AND (vm.attr_id = 10))

(again 11 rows * 8 rows <<< 107283 rows)

postgres=# explain analyze select * from "references" rs left join
"references" vm on vm.reference = rs.reference and vm.attr_id = 10
where rs.object_id = 1000;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------

Nested Loop Left Join (cost=0.00..7.53 rows=107283 width=24) (actual
time=0.077..733.810 rows=117011 loops=1)
-> Index Scan using xif02references on "references" rs
(cost=0.00..0.58 rows=11 width=12) (actual time=0.036..0.079 rows=10
loops=1)

Index Cond: (object_id = 1000)
-> Index Scan using xif01references on "references" vm
(cost=0.00..0.53 rows=8 width=12) (actual time=0.028..37.242
rows=11701 loops=10)
Index Cond: ((vm.reference = rs.reference) AND (vm.attr_id = 10))

On Tue, Feb 15, 2011 at 4:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Maxim Boguk" <Maxim(dot)Boguk(at)gmail(dot)com> writes:
> > I found that strange effect while helping with slow query on russian
> > postgresql online forum.
>
> Please try to put together a self-contained test case for this.
> I could not reproduce such a weird result here, but that probably
> just means there's something strange about your data distribution.
>
> regards, tom lane
>

--
Maxim Boguk
Senior Postgresql DBA.

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.