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 <t...@sss.pgh.pa.us> wrote: > "Maxim Boguk" <maxim.bo...@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 > -- Maxim Boguk Senior Postgresql DBA. Skype: maxim.boguk Jabber: maxim.bo...@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? МойКруг: http://mboguk.moikrug.ru/ Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.