Query tune, index is not using

Поиск
Список
Период
Сортировка
От AI Rumman
Тема Query tune, index is not using
Дата
Msg-id AANLkTi=vKmpg3QmWxo8CXAtCmkL-BO3rzP+Hv77jL2fE@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query tune, index is not using  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Список pgsql-general
I am using two similar queries where one query is using index and other is not.
I don't know why.
explain analyze
select ticketstatus 
from ticketstatus 
inner join role2picklist on role2picklist.picklistvalueid=ticketstatus.picklist_valueid 
where roleid!='H1' order by sortid

  QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=2053.15..2055.23 rows=834 width=422) (actual time=6.929..7.805 rows=952 loops=1)
  Sort Key: role2picklist.sortid
  -> Nested Loop (cost=0.00..2012.68 rows=834 width=422) (actual time=0.151..5.109 rows=952 loops=1)
  -> Seq Scan on ticketstatus (cost=0.00..1.12 rows=12 width=422) (actual time=0.029..0.046 rows=12 loops=1)
  -> Index Scan using role2picklist_picklistvalueid_idx on role2picklist (cost=0.00..166.77 rows=69 width=8) (actual time=0.021..0.251 rows=79 loops=12)
  Index Cond: (role2picklist.picklistvalueid = "outer".picklist_valueid)
  Filter: ((roleid)::text <> 'H1'::text)
 Total runtime: 9.042 ms
(8 rows)


explain analyze
select cf_1507 
from cf_1507 
inner join role2picklist on role2picklist.picklistvalueid=cf_1507.picklist_valueid 
where roleid!='H1' order by sortid

  QUERY PLAN  
-----------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=5989.07..5998.27 rows=3682 width=422) (actual time=545.203..548.895 rows=4037 loops=1)
  Sort Key: role2picklist.sortid
  -> Hash Join (cost=1.66..5350.24 rows=3682 width=422) (actual time=5.817..536.341 rows=4037 loops=1)
  Hash Cond: ("outer".picklistvalueid = "inner".picklist_valueid)
  -> Seq Scan on role2picklist (cost=0.00..4208.30 rows=220692 width=8) (actual time=0.011..297.592 rows=220788 loops=1)
  Filter: ((roleid)::text <> 'H1'::text)
  -> Hash (cost=1.53..1.53 rows=53 width=422) (actual time=0.161..0.161 rows=53 loops=1)
  -> Seq Scan on cf_1507 (cost=0.00..1.53 rows=53 width=422) (actual time=0.020..0.087 rows=53 loops=1)
 Total runtime: 553.567 ms
(9 rows)
\d role2picklist
  Table "public.role2picklist"
  Column | Type | Modifiers 
-----------------+------------------------+-----------
 roleid | character varying(255) | not null
 picklistvalueid | integer | not null
 picklistid | integer | not null
 sortid | integer | 
Indexes:
  "role2picklist_pkey" PRIMARY KEY, btree (roleid, picklistvalueid, picklistid)
  "fk_2_role2picklist" btree (picklistid)
  "role2picklist_picklistvalueid_idx" btree (picklistvalueid)
  "role2picklist_roleid_picklistid_idx" btree (roleid, picklistid, picklistvalueid)

\d cf_1507 
  Table "public.cf_1507"
  Column | Type | Modifiers  
------------------+------------------------+----------------------------------------------------------
 cf_1507id | integer | not null default nextval('cf_1507_seq'::regclass)
 cf_1507 | character varying(200) | not null
 presence | integer | not null default 1
 picklist_valueid | integer | not null default 0
Indexes:
  "cf_1507_pkey" PRIMARY KEY, btree (cf_1507id)


\d ticketstatus
  Table "public.ticketstatus"
  Column | Type | Modifiers  
------------------+------------------------+---------------------------------------------------------------
 ticketstatus_id | integer | not null default nextval('ticketstatus_seq'::regclass)
 ticketstatus | character varying(200) | 
 presence | integer | not null default 0
 picklist_valueid | integer | not null default 0
Indexes:
  "ticketstatus_pkey" PRIMARY KEY, btree (ticketstatus_id)

Any help please.

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

Предыдущее
От: Mike Christensen
Дата:
Сообщение: Windows 2008/IIS cannot connect to Postgres 9.0
Следующее
От: Fujii Masao
Дата:
Сообщение: Re: streaming replication question