Re: simple join uses indexes, very slow - Mailing list pgsql-performance

From Simon Riggs
Subject Re: simple join uses indexes, very slow
Date
Msg-id 1143534654.3839.251.camel@localhost.localdomain
Whole thread Raw
In response to simple join uses indexes, very slow  (george young <gry@ll.mit.edu>)
Responses Re: simple join uses indexes, very slow
List pgsql-performance
On Mon, 2006-03-27 at 13:47 -0500, george young wrote:

> Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
>
> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro, parameters p WHERE ro.run = p.run AND
ro.opset_num= p.opset_num and ro.run='team9'; 
>                                                                  QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop  (cost=2.16..7957.40 rows=6707 width=22) (actual time=14.986..70197.129 rows=43050 loops=1)
>    ->  Index Scan using run_opsets_pkey on run_opsets ro  (cost=0.00..128.75 rows=71 width=18) (actual
time=0.386..62.959rows=263 loops=1) 
>          Index Cond: (run = 'team9'::text)
>    ->  Bitmap Heap Scan on parameters p  (cost=2.16..109.93 rows=27 width=22) (actual time=1.591..266.211 rows=164
loops=263)
>          Recheck Cond: (('team9'::text = p.run) AND ("outer".opset_num = p.opset_num))
>          ->  Bitmap Index Scan on parameters_idx  (cost=0.00..2.16 rows=27 width=0) (actual time=1.153..1.153
rows=164loops=263) 
>                Index Cond: (('team9'::text = p.run) AND ("outer".opset_num = p.opset_num))
>  Total runtime: 70237.727 ms
> (8 rows)

The planner appears to be underestimating the number of rows retrieved
in both cases, then multiplying them together to make it worse.
Multi-column indexes provide less accurate estimates (right now).

Looks like a hash join might be faster. What is your work_mem set to?

Can you SET enable_nestloop=off and rerun the EXPLAIN ANALYZE?

Best Regards, Simon Riggs


pgsql-performance by date:

Previous
From: "Mikael Carneholm"
Date:
Subject: Re: count(*) performance
Next
From: Markus Schaber
Date:
Subject: Re: count(*) performance