From: Simon Riggs <simon@2ndquadrant.com>
To: gry@ll.mit.edu
Cc: pgsql-performance@postgresql.org
Subject: Re: simple join uses indexes, very slow
Date: Tue, 28 Mar 2006 09:30:54 +0100
Message-ID: <1143534654.3839.251.camel@localhost.localdomain> (raw)
In-Reply-To: <20060327134733.72422d23.gry@ll.mit.edu>
References: <20060327134733.72422d23.gry@ll.mit.edu>
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.959 rows=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=164 loops=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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, gry@ll.mit.edu
Subject: Re: simple join uses indexes, very slow
In-Reply-To: <1143534654.3839.251.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox