Re: simple join uses indexes, very slow

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/