From: george young <gry@ll.mit.edu>
To: pgsql-performance@postgresql.org
Subject: Re: simple join uses indexes, very slow
Date: Tue, 28 Mar 2006 15:56:56 -0500
Message-ID: <20060328155656.3f66924c.gry@ll.mit.edu> (raw)
In-Reply-To: <1143569869.32384.37.camel@localhost.localdomain>
References: <20060327134733.72422d23.gry@ll.mit.edu>
<1143534654.3839.251.camel@localhost.localdomain>
<20060328102200.39077862.gry@ll.mit.edu>
<1143569869.32384.37.camel@localhost.localdomain>
On Tue, 28 Mar 2006 19:17:49 +0100
Simon Riggs <simon@2ndquadrant.com> threw this fish to the penguins:
> On Tue, 2006-03-28 at 10:22 -0500, george young wrote:
>
> > work_mem= 1024
>
> Set that higher.
>
> Try a couple of other plans using enable_* and let us have the EXPLAIN
> ANALYZE plans.
I tried this, but it doesn't seem to have made much difference that I can see:
newschm3=> show work_mem;
work_mem
----------
8024
newschm3=> 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=292.739..107672.525 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=115.134..197.818 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=2.559..408.125 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=2.099..2.099 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND ("outer".opset_num = p.opset_num))
Total runtime: 107860.493 ms
(8 rows)
newschm3=> shoe enable_nestloop;
ERROR: syntax error at or near "shoe" at character 1
LINE 1: shoe enable_nestloop;
^
newschm3=> show enable_nestloop;
enable_nestloop
-----------------
on
(1 row)
newschm3=> set enable_nestloop=off;
SET
newschm3=> 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
---------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=34177.87..34291.36 rows=6707 width=22) (actual time=64654.744..64760.875 rows=43050 loops=1)
Merge Cond: ("outer".opset_num = "inner".opset_num)
-> Sort (cost=130.93..131.11 rows=71 width=18) (actual time=62.177..62.333 rows=263 loops=1)
Sort Key: ro.opset_num
-> Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75 rows=71 width=18) (actual time=40.415..55.745 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
-> Sort (cost=34046.94..34070.02 rows=9231 width=22) (actual time=64592.526..64615.228 rows=43050 loops=1)
Sort Key: p.opset_num
-> Bitmap Heap Scan on parameters p (cost=272.31..33438.97 rows=9231 width=22) (actual time=333.975..64126.200 rows=43050 loops=1)
Recheck Cond: ('team9'::text = run)
-> Bitmap Index Scan on parameters_idx (cost=0.00..272.31 rows=9231 width=0) (actual time=309.199..309.199 rows=43050 loops=1)
Index Cond: ('team9'::text = run)
Total runtime: 64919.714 ms
(13 rows)
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
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: gry@ll.mit.edu
Subject: Re: simple join uses indexes, very slow
In-Reply-To: <20060328155656.3f66924c.gry@ll.mit.edu>
* 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