Re: how to change the provoke table in hash join

On Wed, Sep 10, 2014 at 10:05 PM, Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>
wrote:

> --plan 1, 10 seconds were spent on sequential scan on term_weekly table.
>
>
>
> dev=# explain analyze select distinct cs_id from lookup_weekly n inner
> join term_weekly s on s.b_id=n.b_id and s.date=n.date where term in
> ('cat'::text);
>
>
>
>
>
>
> QUERY PLAN
>
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> HashAggregate (cost=2100211.06..2100211.11 rows=5 width=4) (actual
> time=27095.470..27095.487 rows=138 loops=1)
> ...
>
>
>
> --plan 2, only 1 second spent on index scan of term_weekly table, however,
> as it selects the big table to do the hashing, it takes 22 seconds for the
> hash to complete. The advantage get from index has been totally lost
> because of this join order.
>
>
>
>
> QUERY PLAN
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> HashAggregate (cost=1429795.17..1429795.22 rows=5 width=4) (actual
> time=22991.289..22991.307 rows=138 loops=1)
> ...

Am I reading something wrong here? I haven't looked all the plan, but the
second is faster (overall), so why do you think you need a hint or change
what the planner choose? For me looks like using the index is the best for
this situation. Could you try running this multiple times and taking the
min/max/avg time of both?

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres