Re: simple join uses indexes, very slow - Mailing list pgsql-performance
From | Chris |
---|---|
Subject | Re: simple join uses indexes, very slow |
Date | |
Msg-id | 442A1808.6090705@gmail.com Whole thread Raw |
In response to | simple join uses indexes, very slow (george young <gry@ll.mit.edu>) |
Responses |
Re: simple join uses indexes, very slow
|
List | pgsql-performance |
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.959rows=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=164loops=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/
pgsql-performance by date: