If your looking for suggestions, I would suggest updating the 8.1.x you
have installed to the latest version, as of typing this is 8.1.3 ;) Most
notable is some of the -bug- fixes that are in since 8.1.0, for example;
* Fix incorrect optimizations of outer-join conditions (Tom)
You know, minor point releases aren't adding new features or changing
basic functionality, they are pure and simple bugfixes. If I was in
-your- position, I would run (don't walk ;) and install upto 8.1.3
of course, thats jst my 2c, feel free to ignore :D
Regards
Stef
Chris wrote:
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?
