Re: Query tune, index is not using

On 5 Oct 2010, at 10:21, AI Rumman wrote:

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

A few indexes on role2picklist.sortid and cf_1507.picklist_valueid would probably help.

> \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)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4cab661b678301651414001!