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, referenc=
e );

analyze "references";

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

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

On my system (8.4.4) it producing next results:

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

                                          QUERY PLAN
---------------------------------------------------------------------------=
--------------------
 Nested Loop Left Join  (cost=3D0.00..7.53 rows=3D107283 width=3D24)

   ->  Index Scan using xif02references on "references" rs
(cost=3D0.00..0.58 rows=3D11 width=3D12)
         Index Cond: (object_id =3D 1000)
   ->  Index Scan using xif01references on "references" vm
(cost=3D0.00..0.53 rows=3D8 width=3D12)

         Index Cond: ((vm.reference =3D rs.reference) AND (vm.attr_id =3D 1=
0))

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


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


QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------------------------------------

 Nested Loop Left Join  (cost=3D0.00..7.53 rows=3D107283 width=3D24) (actual
time=3D0.077..733.810 rows=3D117011 loops=3D1)
   ->  Index Scan using xif02references on "references" rs
(cost=3D0.00..0.58 rows=3D11 width=3D12) (actual time=3D0.036..0.079 rows=
=3D10
loops=3D1)

         Index Cond: (object_id =3D 1000)
   ->  Index Scan using xif01references on "references" vm
(cost=3D0.00..0.53 rows=3D8 width=3D12) (actual time=3D0.028..37.242
rows=3D11701 loops=3D10)
         Index Cond: ((vm.reference =3D rs.reference) AND (vm.attr_id =3D 1=
0))

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

> "Maxim Boguk" <Maxim.Boguk@gmail.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
>



--=20
Maxim Boguk
Senior Postgresql DBA.

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.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?

=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/
=D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0=BE=
=D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 =D0=
=BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0=BE=
=D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 =D0=
=B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5
=D0=B2=D1=81=D0=B5.