simple join uses indexes, very slow

Поиск
Список
Период
Сортировка
От george young
Тема simple join uses indexes, very slow
Дата
Msg-id 20060327134733.72422d23.gry@ll.mit.edu
обсуждение исходный текст
Ответы Re: simple join uses indexes, very slow  (Simon Riggs <simon@2ndquadrant.com>)
Re: simple join uses indexes, very slow  ("Dave Dutcher" <dave@tridecap.com>)
Re: simple join uses indexes, very slow  (Chris <dmagick@gmail.com>)
Список pgsql-performance
[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=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)


"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Gábriel Ákos
Дата:
Сообщение: Re: count(*) performance
Следующее
От: Brendan Duddridge
Дата:
Сообщение: Re: count(*) performance