Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-23 Thread Gaetano Mendola
Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: No, postgres didn't do things in reverse order. It hashed the empty table and then went ahead and checked every record of the non-empty table against the empty hash table. Reading the code there's no check for this, and it seems like it would

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Gaetano Mendola
Dennis Bjorklund wrote: On Wed, 22 Sep 2004, Gaetano Mendola wrote: Now my question is why the 7.4 choose the hash join ? :-( It looks to me that the marge join is faster because there wasn't really anything to merge, it resulted in 0 rows. Maybe the hash join that is choosen in 7.4 would

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Dennis Bjorklund
On Wed, 22 Sep 2004, Gaetano Mendola wrote: Limit (cost=10.21..10.21 rows=1 width=24) (actual time=0.885..0.885 rows=0 loops=1) Limit (cost=10.28..10.28 rows=1 width=24) (actual time=0.429..0.429 rows=0 loops=1) These estimated costs are almost the same, but the runtime differs a

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Gaetano Mendola
Dennis Bjorklund wrote: On Wed, 22 Sep 2004, Gaetano Mendola wrote: Limit (cost=10.21..10.21 rows=1 width=24) (actual time=0.885..0.885 rows=0 loops=1) Limit (cost=10.28..10.28 rows=1 width=24) (actual time=0.429..0.429 rows=0 loops=1) These estimated costs are almost the same, but

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Gaetano Mendola [EMAIL PROTECTED] writes: hash_join = on - Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (actual time=0.017..0.038 rows=16 loops=1) hash_join = off - Seq Scan on lookup_tipo_evento le (cost=0.00..1.16

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Dennis Bjorklund
On 22 Sep 2004, Greg Stark wrote: Actually this looks like it's arguably a bug to me. Why does the hash join execute the sequential scan at all? Shouldn't it also like the merge join recognize that the other hashed relation is empty and skip the sequential scan entirely? I'm not sure you can

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Dennis Bjorklund [EMAIL PROTECTED] writes: On 22 Sep 2004, Greg Stark wrote: Actually this looks like it's arguably a bug to me. Why does the hash join execute the sequential scan at all? Shouldn't it also like the merge join recognize that the other hashed relation is empty and skip

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: No, postgres didn't do things in reverse order. It hashed the empty table and then went ahead and checked every record of the non-empty table against the empty hash table. Reading the code there's no check for this, and it seems like it would be a useful

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Yeah, I was just looking at doing that. Well I imagine it takes you as long to read my patch as it would for you to write it. But anyways it's still useful to me as exercises. It would also be interesting to prefetch one row from the outer table and fall

Re: [PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-22 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: It would also be interesting to prefetch one row from the outer table and fall out immediately (without building the hash table) if the outer table is empty. This seems to require some contortion of the code though :-( Why is it any more complicated than

[PERFORM] 7.4 vs 7.3 ( hash join issue )

2004-09-21 Thread Gaetano Mendola
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm having performance degradation with a view upgrading from 7.3 to 7.4, the view is a not so complex, one of his field is the result from a function. If I remove the function ( or I use a void function ) the 7.4 out perform the 7.3: On 7.4 I