Thread: 7.4 vs 7.3 ( hash join issue )
-----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 get:
xxxxx=# explain analyze select * from v_ivan_2;
~ QUERY PLAN
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~ Hash Left Join (cost=7028.36..16780.89 rows=65613 width=288) (actual time=2059.923..9340.043 rows=79815 loops=1)
~ Hash Cond: ("outer".id_baa_loc = "inner".id_baa_loc)
~ -> Hash Left Join (cost=6350.62..15134.25 rows=65613 width=258) (actual time=1816.013..7245.085 rows=65609
loops=1)
~ Hash Cond: ("outer".id_localita = "inner".id_localita)
~ -> Hash Left Join (cost=6252.93..14786.74 rows=65613 width=247) (actual time=1777.072..6533.316 rows=65609
loops=1)
~ Hash Cond: ("outer".id_frazione = "inner".id_frazione)
~ -> Hash Left Join (cost=6226.61..14362.74 rows=65613 width=235) (actual time=1768.273..5837.104
rows=65609loops=1)
~ Hash Cond: ("outer".id_baa = "inner".id_baa)
~ -> Hash Left Join (cost=5092.24..12342.65 rows=65594 width=197) (actual time=1354.059..4562.398
rows=65592loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Hash Left Join (cost=3597.52..10010.84 rows=65594 width=173) (actual
time=785.775..3278.372rows=65592 loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Hash Join (cost=1044.77..6605.97 rows=65594 width=149) (actual
time=274.316..2070.788rows=65592 loops=1)
~ Hash Cond: ("outer".id_stato_pratica = "inner".id_stato_pratica)
~ -> Hash Join (cost=1043.72..5850.59 rows=65593 width=141) (actual
time=273.478..1421.274rows=65592 loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Seq Scan on t_pratica p (cost=0.00..3854.27 rows=65927 width=137)
(actualtime=7.275..533.281 rows=65927 loops=1)
~ -> Hash (cost=1010.92..1010.92 rows=65592 width=8) (actual
time=265.615..265.615rows=0 loops=1)
~ -> Seq Scan on t_baa_pratica bp (cost=0.00..1010.92 rows=65592
width=8)(actual time=0.209..164.761 rows=65592 loops=1)
~ -> Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.254..0.254 rows=0
loops=1)
~ -> Seq Scan on lookup_stato_pratica s (cost=0.00..1.05 rows=5 width=22)
(actualtime=0.190..0.210 rows=5 loops=1)
~ -> Hash (cost=2519.82..2519.82 rows=65865 width=28) (actual time=511.104..511.104
rows=0loops=1)
~ -> Seq Scan on t_persona (cost=0.00..2519.82 rows=65865 width=28) (actual
time=0.068..381.586rows=65864 loops=1)
~ Filter: (is_rich = true)
~ -> Hash (cost=1462.53..1462.53 rows=64356 width=28) (actual time=567.919..567.919 rows=0
loops=1)
~ -> Index Scan using idx_t_persona_is_inte on t_persona (cost=0.00..1462.53
rows=64356width=28) (actual time=12.953..432.697 rows=64356 loops=1)
~ Index Cond: (is_inte = true)
~ -> Hash (cost=1113.65..1113.65 rows=41444 width=46) (actual time=413.782..413.782 rows=0
loops=1)
~ -> Hash Join (cost=4.33..1113.65 rows=41444 width=46) (actual time=2.687..333.746
rows=41444loops=1)
~ Hash Cond: ("outer".id_comune = "inner".id_comune)
~ -> Seq Scan on t_baa_loc bl (cost=0.00..653.44 rows=41444 width=20) (actual
time=0.422..94.803rows=41444 loops=1)
~ -> Hash (cost=4.22..4.22 rows=222 width=34) (actual time=1.735..1.735 rows=0
loops=1)
~ -> Seq Scan on t_comune co (cost=0.00..4.22 rows=222 width=34) (actual
time=0.521..1.277rows=222 loops=1)
~ -> Hash (cost=25.59..25.59 rows=1459 width=20) (actual time=8.343..8.343 rows=0 loops=1)
~ -> Seq Scan on t_frazione f (cost=0.00..25.59 rows=1459 width=20) (actual time=0.554..5.603
rows=1459loops=1)
~ -> Hash (cost=94.94..94.94 rows=5494 width=19) (actual time=38.504..38.504 rows=0 loops=1)
~ -> Seq Scan on t_localita l (cost=0.00..94.94 rows=5494 width=19) (actual time=8.499..28.216
rows=5494loops=1)
~ -> Hash (cost=660.61..660.61 rows=34261 width=38) (actual time=198.663..198.663 rows=0 loops=1)
~ -> Seq Scan on t_affaccio af (cost=0.00..660.61 rows=34261 width=38) (actual time=5.875..133.336 rows=34261
loops=1)
~ Total runtime: 9445.263 ms
(40 rows)
On 7.3 I get:
xxxxx=# explain analyze select * from v_ivan_2;
~ QUERY PLAN
-
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~ Hash Join (cost=5597.02..15593.91 rows=65610 width=354) (actual time=2169.37..13102.64 rows=79815 loops=1)
~ Hash Cond: ("outer".id_baa_loc = "inner".id_baa_loc)
~ -> Hash Join (cost=4919.28..13953.00 rows=65610 width=316) (actual time=1966.38..10568.69 rows=65609 loops=1)
~ Hash Cond: ("outer".id_localita = "inner".id_localita)
~ -> Hash Join (cost=4821.59..13596.30 rows=65610 width=297) (actual time=1934.29..9151.45 rows=65609
loops=1)
~ Hash Cond: ("outer".id_frazione = "inner".id_frazione)
~ -> Hash Join (cost=4795.27..13157.36 rows=65610 width=277) (actual time=1925.29..7795.71 rows=65609
loops=1)
~ Hash Cond: ("outer".id_baa = "inner".id_baa)
~ -> Hash Join (cost=3640.17..11149.38 rows=65592 width=223) (actual time=1375.66..5870.74
rows=65592loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Hash Join (cost=3597.53..10237.66 rows=65592 width=195) (actual time=835.95..4332.46
rows=65592loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Hash Join (cost=1044.78..6800.07 rows=65592 width=167) (actual
time=307.55..2903.04rows=65592 loops=1)
~ Hash Cond: ("outer".id_pratica = "inner".id_pratica)
~ -> Merge Join (cost=1.06..4770.96 rows=65927 width=159) (actual
time=1.41..1898.12rows=65927 loops=1)
~ Merge Cond: ("outer".id_stato_pratica = "inner".id_stato_pratica)
~ -> Index Scan using idx_t_pratica on t_pratica p (cost=0.00..4044.70
rows=65927width=137) (actual time=0.58..894.95 rows=65927 loops=1)
~ -> Sort (cost=1.06..1.06 rows=5 width=22) (actual time=0.78..58.49
rows=63528loops=1)
~ Sort Key: s.id_stato_pratica
~ -> Seq Scan on lookup_stato_pratica s (cost=0.00..1.05 rows=5
width=22)(actual time=0.11..0.13 rows=5 loops=1)
~ -> Hash (cost=1010.92..1010.92 rows=65592 width=8) (actual
time=305.40..305.40rows=0 loops=1)
~ -> Seq Scan on t_baa_pratica bp (cost=0.00..1010.92 rows=65592 width=8)
(actualtime=0.23..192.88 rows=65592 loops=1)
~ -> Hash (cost=2519.82..2519.82 rows=65864 width=28) (actual time=527.88..527.88
rows=0loops=1)
~ -> Seq Scan on t_persona (cost=0.00..2519.82 rows=65864 width=28) (actual
time=0.07..394.51rows=65864 loops=1)
~ Filter: (is_rich = true)
~ -> Hash (cost=10.46..10.46 rows=64356 width=28) (actual time=539.27..539.27 rows=0
loops=1)
~ -> Index Scan using idx_t_persona_is_inte on t_persona (cost=0.00..10.46 rows=64356
width=28)(actual time=0.61..403.48 rows=64356 loops=1)
~ Index Cond: (is_inte = true)
~ -> Hash (cost=1134.38..1134.38 rows=41444 width=54) (actual time=549.25..549.25 rows=0 loops=1)
~ -> Hash Join (cost=4.33..1134.38 rows=41444 width=54) (actual time=2.19..470.20
rows=41444loops=1)
~ Hash Cond: ("outer".id_comune = "inner".id_comune)
~ -> Seq Scan on t_baa_loc bl (cost=0.00..653.44 rows=41444 width=20) (actual
time=0.15..179.24rows=41444 loops=1)
~ -> Hash (cost=4.22..4.22 rows=222 width=34) (actual time=1.55..1.55 rows=0 loops=1)
~ -> Seq Scan on t_comune co (cost=0.00..4.22 rows=222 width=34) (actual
time=0.22..1.08rows=222 loops=1)
~ -> Hash (cost=25.59..25.59 rows=1459 width=20) (actual time=8.37..8.37 rows=0 loops=1)
~ -> Seq Scan on t_frazione f (cost=0.00..25.59 rows=1459 width=20) (actual time=0.22..5.46
rows=1459loops=1)
~ -> Hash (cost=94.94..94.94 rows=5494 width=19) (actual time=31.46..31.46 rows=0 loops=1)
~ -> Seq Scan on t_localita l (cost=0.00..94.94 rows=5494 width=19) (actual time=0.22..20.41 rows=5494
loops=1)
~ -> Hash (cost=660.61..660.61 rows=34261 width=38) (actual time=199.96..199.96 rows=0 loops=1)
~ -> Seq Scan on t_affaccio af (cost=0.00..660.61 rows=34261 width=38) (actual time=0.21..130.67 rows=34261
loops=1)
~ Total runtime: 13190.70 msec
(41 rows)
As you can see the 7.3 do a index scan on the table t_pratica and the 7.4 perform a sequential scan,
the plans however are very close to each other.
So I identify the performance issue on the function call, indeed:
7.4:
xxxxx=# explain analyze select sp_foo(id_pratica) from t_pratica;
~ QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------
~ Seq Scan on t_pratica (cost=0.00..3887.23 rows=65927 width=4) (actual time=4.013..45240.015 rows=65927 loops=1)
~ Total runtime: 45499.123 ms
(2 rows)
7.3:
xxxxx=# explain analyze select sp_foo(id_pratica) from t_pratica;
~ QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------
~ Seq Scan on t_pratica (cost=0.00..3854.27 rows=65927 width=4) (actual time=0.58..18446.99 rows=65927 loops=1)
~ Total runtime: 18534.41 msec
(2 rows)
This is the sp_foo:
CREATE FUNCTION sp_foo (integer) RETURNS text
~ AS '
DECLARE
~ a_id_pratica ALIAS FOR $1;
~ my_parere TEXT;
BEGIN
~ a_id_pratica := $1;
~ SELECT INTO my_parere le.nome
~ FROM t_evento e,
~ lookup_tipo_evento le
~ WHERE e.id_tipo_evento = le.id_tipo_evento AND
~ e.id_pratica = a_id_pratica AND
~ e.id_tipo_evento in (5,6,7,8 )
~ ORDER by e.id_evento desc
~ LIMIT 1;
~ RETURN my_parere;
END;
' LANGUAGE plpgsql;
Preparing a statement this is the plan used by 7.4:
xxxxx=# explain analyze execute foo_body( 5 );
~ QUERY PLAN
-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~ Limit (cost=10.30..10.30 rows=1 width=24) (actual time=0.538..0.538 rows=0 loops=1)
~ -> Sort (cost=10.30..10.30 rows=1 width=24) (actual time=0.534..0.534 rows=0 loops=1)
~ Sort Key: e.id_evento
~ -> Hash Join (cost=9.11..10.30 rows=1 width=24) (actual time=0.512..0.512 rows=0 loops=1)
~ Hash Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento)
~ -> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (actual time=0.010..0.041
rows=16loops=1)
~ -> Hash (cost=9.11..9.11 rows=1 width=16) (actual time=0.144..0.144 rows=0 loops=1)
~ -> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.11 rows=1 width=16)
(actualtime=0.140..0.140 rows=0 loops=1)
~ Index Cond: (id_pratica = $1)
~ Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR
((id_tipo_evento)::text= '7'::text) OR ((id_tipo_evento)::text = '8'::text))
~ Total runtime: 0.824 ms
(11 rows)
The table t_pratica have 65927 rows so 0.824 ms * 65927 is almost the total time execution for
each t_pratica row ~ 45000 ms
Unfortunately I can not see the plan used by the 7.3 engine due the lack of explain execute,
however I did an explain analyze on the select:
xxxxx=# explain analyze SELECT le.nome
xxxxx-# FROM t_evento e,lookup_tipo_evento le
xxxxx-# WHERE e.id_tipo_evento = le.id_tipo_evento
xxxxx-# AND e.id_pratica = 5
xxxxx-# AND e.id_tipo_evento in (5,6,7,8 )
xxxxx-# ORDER by e.id_evento desc
xxxxx-# LIMIT 1;
~ QUERY PLAN
-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~ Limit (cost=10.27..10.27 rows=1 width=48) (actual time=0.19..0.19 rows=0 loops=1)
~ -> Sort (cost=10.27..10.27 rows=1 width=48) (actual time=0.18..0.18 rows=0 loops=1)
~ Sort Key: e.id_evento
~ -> Merge Join (cost=10.24..10.27 rows=1 width=48) (actual time=0.09..0.09 rows=0 loops=1)
~ Merge Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento)
~ -> Sort (cost=9.02..9.02 rows=1 width=16) (actual time=0.09..0.09 rows=0 loops=1)
~ Sort Key: e.id_tipo_evento
~ -> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.02 rows=1 width=16)
(actualtime=0.06..0.06 rows=0 loops=1)
~ Index Cond: (id_pratica = 5)
~ Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR
((id_tipo_evento)::text= '7'::text) OR ((id_tipo_evento)::text = '8'::text))
~ -> Sort (cost=1.22..1.23 rows=16 width=32) (never executed)
~ Sort Key: le.id_tipo_evento
~ -> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (never executed)
~ Total runtime: 0.31 msec
(14 rows)
Disabling the hashjoin on the 7.4 I got best performance that 7.3:
xxxxx=# set enable_hashjoin = off;
SET
xxxxx=# explain analyze select sp_get_ultimo_parere(id_pratica) from t_pratica;
~ QUERY PLAN
- -------------------------------------------------------------------------------------------------------------------
~ Seq Scan on t_pratica (cost=0.00..3887.23 rows=65927 width=4) (actual time=12.384..12396.136 rows=65927 loops=1)
~ Total runtime: 12485.548 ms
(2 rows)
Now my question is why the 7.4 choose the hash join ? :-(
I can provide further details if you ask
BTW with the hash_join = off the 7.4 choose the same 7.3 plan for this function body.
Of course both engines are running on the same machine with the same settings.
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBULCU7UpzwH2SGd4RAt2ZAKC9FjAKiljRqgaZSZa+p/7N65Cl7ACePWBV
TaR2VH1kDSBS7b+kNK4deFo=
=X+th
-----END PGP SIGNATURE-----
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 have been faster had there been a couple of result rows (just a guess). It would be interesting to compare the plans in 7.4 with and without hash_join active and see what costs it estimates for a merge join compared to a hash join. -- /Dennis Björklund
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 have been faster had there been a couple of result
> rows (just a guess).
>
> It would be interesting to compare the plans in 7.4 with and without
> hash_join active and see what costs it estimates for a merge join compared
> to a hash join.
Here they are:
hash_join = on
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.21..10.21 rows=1 width=24) (actual time=0.885..0.885 rows=0 loops=1)
-> Sort (cost=10.21..10.21 rows=1 width=24) (actual time=0.880..0.880 rows=0 loops=1)
Sort Key: e.id_evento
-> Hash Join (cost=9.02..10.21 rows=1 width=24) (actual time=0.687..0.687 rows=0 loops=1)
Hash Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento)
-> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (actual time=0.017..0.038
rows=16loops=1)
-> Hash (cost=9.02..9.02 rows=1 width=16) (actual time=0.212..0.212 rows=0 loops=1)
-> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.02 rows=1 width=16)
(actualtime=0.208..0.208 rows=0 loops=1)
Index Cond: (id_pratica = 5)
Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR
((id_tipo_evento)::text= '7'::text) OR ((id_tipo_evento)::text = '8'::text))
Total runtime: 1.244 ms
(11 rows)
hash_join = off
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.28..10.28 rows=1 width=24) (actual time=0.429..0.429 rows=0 loops=1)
-> Sort (cost=10.28..10.28 rows=1 width=24) (actual time=0.425..0.425 rows=0 loops=1)
Sort Key: e.id_evento
-> Merge Join (cost=10.25..10.27 rows=1 width=24) (actual time=0.218..0.218 rows=0 loops=1)
Merge Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento)
-> Sort (cost=9.02..9.02 rows=1 width=16) (actual time=0.214..0.214 rows=0 loops=1)
Sort Key: e.id_tipo_evento
-> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.02 rows=1 width=16)
(actualtime=0.110..0.110 rows=0 loops=1)
Index Cond: (id_pratica = 5)
Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR
((id_tipo_evento)::text= '7'::text) OR ((id_tipo_evento)::text = '8'::text))
-> Sort (cost=1.22..1.23 rows=16 width=32) (never executed)
Sort Key: le.id_tipo_evento
-> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (never executed)
Total runtime: 0.721 ms
(14 rows)
Regards
Gaetano Mendola
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 bit. This means that maybe you need to alter settings like random_page_cost, effective_cache and maybe some others to make the cost reflect the runtime better. Since the costs are so close to each other very small changes can make it choose the other plan. It's also very hard to make an estimate that is correct in all situations. That's why it's called an estimate after all. -- /Dennis Björklund
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 the runtime differs a bit.
> This means that maybe you need to alter settings like random_page_cost,
> effective_cache and maybe some others to make the cost reflect the runtime
> better.
>
> Since the costs are so close to each other very small changes can make it
> choose the other plan. It's also very hard to make an estimate that is
> correct in all situations. That's why it's called an estimate after all.
Is not feseable.
That values are obtained with random_page_cost = 2, effective_cache_size = 20000,
cpu_tuple_cost = 0.01
increasing or decreasing random_page_cost this means increase or decrease both
costs:
random_page_cost = 1.5
hashjoin on => 8.47
hashjoin off => 8.53
random_page_cost = 3
hashjoin on => 13.70
hashjoin off => 13.76
so is choosen the hasjoin method in both cases.
In the other side the effective_cache_size doesn't affect this costs.
Decreasing the cpu_tuple_cost have the same effect
cpu_tuple_cost = 0.005
hashjoin on => 10.11
hashjoin off => 10.17
cpu_tuple_cost = 0.001
hashjoin on => 10.03
hashjoin off => 10.03
cpu_tuple_cost = 0.0005
hashjoin on => 10.01
hashjoin off => 10.01
And when the two costs are the same the hashjoin path is choosen.
I think cpu_tuple_cost less then 0.001 is not a good idea
I think the only way is set the hashjoin = off. Any other suggestion ?
Regards
Gaetano Mendola
Gaetano Mendola <mendola@bigfoot.com> 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=16loops=1) > > hash_join = off > -> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (never executed) 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? -- greg
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 classify that as a bug. It's just that he in one of the plans started with the empty scan and bacause of that didn't need the other, but with the hash join it started with the table that had 16 rows and then got to the empty one. While I havn't checked, I assume that if it had started with the empty table there then it would have skipped the other. I don't know what criteria is used to select which part to start with when doing a hash join. Looks like it started with the one that had the highest estimate of rows here, doing it the other way around might be a good idea because you in some cases are lucky to find an empty scans and can omit the other. The above are just observations of the behaviour, I've not seen the source at all. -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> 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 > > the sequential scan entirely? > > I'm not sure you can classify that as a bug. It's just that he in one of > the plans started with the empty scan and bacause of that didn't need > the other, but with the hash join it started with the table that had 16 > rows and then got to the empty one. 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 low-cost little optimization. I think postgres normally hashes the table it thinks is smaller, so you do join against an empty relation it should end up on the hash side of the hash join and allow postgres to avoid the scan of the outer table. -- greg
Greg Stark <gsstark@mit.edu> 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 low-cost little optimization.
Yeah, I was just looking at doing that.
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 :-(
> I think postgres normally hashes the table it thinks is smaller,
Right, it will prefer to put the physically smaller table (estimated
width*rows) on the inside.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> 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
> 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 just moving the hash build down lower?
There's one small special case needed in ExecHashJoinOuterGetTuple but it's
pretty non-intrusive.
It seems to work for me but I can't test multiple batches easily. I think I've
convinced myself that they would work fine but...
test=# explain analyze select * from a natural join b;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Hash Join (cost=22.50..345.00 rows=5000 width=4) (actual time=0.005..0.005 rows=0 loops=1)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on a (cost=0.00..20.00 rows=1000 width=4) (actual time=0.002..0.002 rows=0 loops=1)
-> Hash (cost=20.00..20.00 rows=1000 width=4) (never executed)
-> Seq Scan on b (cost=0.00..20.00 rows=1000 width=4) (never executed)
Total runtime: 0.070 ms
(6 rows)
--
greg
Attachment
Greg Stark <gsstark@mit.edu> 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 just moving the hash build down lower?
Having to inject the consideration into ExecHashJoinOuterGetTuple seems
messy to me.
On reflection I'm not sure it would be a win anyway, for a couple of reasons.
(1) Assuming that the planner has gotten things right and put the larger
relation on the outside, the case of an empty outer relation and a
nonempty inner one should rarely arise.
(2) Doing this would lose some of the benefit from the optimization to
detect an empty inner relation. If the outer subplan is a slow-start
one (such as another hashjoin), it would lose a lot of the benefit :-(
regards, tom lane
Tom Lane wrote: > Greg Stark <gsstark@mit.edu> 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 low-cost little optimization. > > > Yeah, I was just looking at doing that. > > 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 :-( > > >>I think postgres normally hashes the table it thinks is smaller, > > > Right, it will prefer to put the physically smaller table (estimated > width*rows) on the inside. Do you plan to do a patch for the 7.4, so I'll wait for a 7.4.6 ( that IIRC have already two important patches pending ) or is 8.0 stuff ? Regards Gaetano Mendola