Re: how to change the provoke table in hash join

[Matheus de Oliveira <matioli.matheus@xxxxxxxxx>]

On Wed, Sep 10, 2014 at 10:05 PM, Huang, Suya <Suya.Huang@xxxxxxxxxxxxxxx> 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