X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6EA4111F66FD for ; Wed, 29 Mar 2006 02:11:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32365-07 for ; Wed, 29 Mar 2006 02:11:48 -0400 (AST) X-Greylist: delayed 00:06:39.759644 by SQLgrey- Received: from smtpout06-04.prod.mesa1.secureserver.net (smtpout06-01.prod.mesa1.secureserver.net [64.202.165.224]) by postgresql.org (Postfix) with SMTP id EFD0D11F665C for ; Wed, 29 Mar 2006 02:11:45 -0400 (AST) Received: (qmail 28265 invoked from network); 29 Mar 2006 06:05:05 -0000 Received: from unknown (69.199.39.70) by smtpout06-04.prod.mesa1.secureserver.net (64.202.165.227) with ESMTP; 29 Mar 2006 06:05:05 -0000 Message-ID: <442A244F.4030203@ummon.com> Date: Wed, 29 Mar 2006 01:08:15 -0500 From: stef User-Agent: Mozilla Thunderbird 1.0.7-4mdk (X11/20051221) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Chris CC: pgsql-performance@postgresql.org Subject: Re: simple join uses indexes, very slow References: <20060327134733.72422d23.gry@ll.mit.edu> <442A1808.6090705@gmail.com> In-Reply-To: <442A1808.6090705@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/572 X-Sequence-Number: 18009 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? > >