DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=QJjdRQdr5TQPPMEBXjgWI7NReYxWODV8oyyG9THRNufUYBYh10VYFF0L74dJGjHiPOx4E9cRt5Y5xhdRC7Z5vxmqribb5y3Y1IN/pYHsbZMrytqTzgsL6/SOO2nLEyJrOdDHHa0ukrfSPBmXnb7IJRr9lFstPKE3Da7vN7HdSZo=
Message-ID: <442A1808.6090705@gmail.com>
Date: Wed, 29 Mar 2006 16:15:52 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: simple join uses indexes, very slow
References: <20060327134733.72422d23.gry@ll.mit.edu>
In-Reply-To: <20060327134733.72422d23.gry@ll.mit.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

george young wrote:
> [PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
> I have a simple join on two tables that takes way too long.  Can you help
> me understand what's wrong?  There are indexes defined on the relevant columns.
> I just did a fresh vacuum --full --analyze on the two tables.
> Is there something I'm not seeing?
> [CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
> -- George Young
> 
> 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)
> 
>                      Table "public.run_opsets"
>     Column    |            Type             |        Modifiers
> --------------+-----------------------------+-------------------------
>  run          | text                        | not null
>  opset        | text                        |
>  opset_ver    | integer                     |
>  opset_num    | integer                     | not null
>  status       | opset_status                |
>  date_started | timestamp without time zone |
>  date_done    | timestamp without time zone |
>  work_started | timestamp without time zone |
>  lock_user    | text                        | default 'NO-USER'::text
>  lock_pid     | integer                     |
>  needs_review | text                        |
> Indexes:
>     "run_opsets_pkey" PRIMARY KEY, btree (run, opset_num) CLUSTER
> 
> 
> --              Table "public.parameters"
>   Column   |  Type   |           Modifiers
> -----------+---------+-------------------------------
>  run       | text    | not null
>  opset_num | integer | not null
>  opset     | text    | not null
>  opset_ver | integer | not null
>  step_num  | integer | not null
>  step      | text    | not null
>  step_ver  | integer | not null
>  name      | text    | not null
>  value     | text    |
>  split     | boolean | not null default false
>  wafers    | text[]  | not null default '{}'::text[]
> Indexes:
>     "parameters_idx" btree (run, opset_num, step_num, opset, opset_ver, step, step_ver, name, split, wafers)
>     "parameters_opset_idx" btree (opset, step, name)
>     "parameters_step_idx" btree (step, name)

More for my own information (because nobody else has suggested it), 
would it make a difference if 'run' was a varchar field rather than text?


-- 
Postgresql & php tutorials
http://www.designmagick.com/