Skip site navigation (1) Skip section navigation (2)

Re: Nested Loop Left Join always shows rows=1

From: Scara Maccai <m_lists(at)yahoo(dot)it>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: postgresql <pgsql-general(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Nested Loop Left Join always shows rows=1
Date: 2008-11-28 05:12:50
Message-ID: 492F7DD2.70806@yahoo.it (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Tom Lane wrote:
> Scara Maccai <m_lists(at)yahoo(dot)it> writes:
>>           ->  Index Scan using id_idx on tab1  (cost=0.00..8.27 rows=1 
>> width=4) (actual time=0.010..0.011 rows=1 loops=1)
>>                 Index Cond: (id = 10)
>>           ->  Index Scan using out_id_idx on tab_outer  (cost=0.00..8.83 
>> rows=1 width=8) (actual time=8.590..11.924 rows=2953 loops=1)
>>                 Index Cond: ((tab_outer.out_id = 10) AND (tab1.id = 
>> tab_outer.out_id))
>
> This seems a bit broken :-( ... ideally it shouldn't be generating the
> redundant index condition, either.  

Why is the index condition redundant? I guess the redundant part is

"AND (tab1.id = tab_outer.out_id)" but I'm not sure, and I would really appreciate if you could explain a little bit more...

The reason I'm asking is because I'm looking at the implementation of some VERY basic form of progress indicator based on "estimated rows vs rows already processed".
I guess this would work for our DB because the planner is usually very good at guessing the number of rows for each step of the plan, since our distributions are pretty simple.

But this left outer join thing I'm afraid would invalidate the method, since the inner table row count is always off...



Thank you for the reply.

(I added hackers too, since this seems a place where some 
fixing/development would be necessary. I hope it's ok.)




In response to

pgsql-hackers by date

Next:From: KaiGai KoheiDate: 2008-11-28 05:21:10
Subject: Re: A bug with ALTER TABLE SET WITHOUT OIDS in CVS HEAD
Previous:From: Fujii MasaoDate: 2008-11-28 03:43:40
Subject: Re: New trigger file in pg_standby to promote the standby to the primary

pgsql-general by date

Next:From: Abdul RahmanDate: 2008-11-28 05:57:02
Subject: Re: PgAgent Job Scehduler is NOT running
Previous:From: Abdul RahmanDate: 2008-11-28 05:12:23
Subject: Re: PgAgent Job Scehduler is NOT running

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group