Re: simple join uses indexes, very slow - Mailing list pgsql-performance

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?
>
>