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 <[email protected]> wrote:
> "Maxim Boguk" <[email protected]> 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: [email protected]
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/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.
