Re: Indexes

Jake Stride wrote:
> Hi,
>
> I have a table set up:
>
> \d companycontactmethod
> Table "public.companycontactmethod"
> Column | Type | Modifiers
> -----------+-------------------+------------------------
> tag | character varying | not null
> contact | character varying | not null
> type | character(1) | not null
> companyid | bigint | not null
> name | character varying | not null
> main | boolean | not null default false
> billing | boolean | not null default false
> shipping | boolean | not null default false
> payment | boolean | not null default false
> technical | boolean | not null default false
> Indexes:
> "companycontactmethod_pkey" PRIMARY KEY, btree (companyid, name, "type")
> "companycontactmethod_companyid" btree (companyid)
> "companycontactmethod_main_type" btree (main, "type")
> Foreign-key constraints:
> "$1" FOREIGN KEY (companyid) REFERENCES company(id) ON UPDATE
> CASCADE ON DELETE CASCADE
>
> and am running the following:
>
> explain analyse SELECT companycontactmethod.tag,
> companycontactmethod.contact, companycontactmethod."type",
> companycontactmethod.companyid FROM companycontactmethod WHERE
> companycontactmethod.main AND companycontactmethod.type = 'E';
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------
> Seq Scan on companycontactmethod (cost=0.00..181.10 rows=2079
> width=40) (actual time=0.027..17.068 rows=2134 loops=1)
> Filter: (main AND ("type" = 'E'::bpchar))
> Total runtime: 25.965 ms
>
> why is it not using the companycontactmethod_main_type index on the
> query? Am I missing something obvious here?
>
> Thanks
>
> Jake
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

The index is of no use when you specify no value for main. You want any
row that has any value for main, and a value of 'E' for type. Because
you haven't specified a value for 'main' the only solution is to scan
the entire set.

Pete
--
Peter Wilson, YellowHawk Ltd, http://www.yellowhawk.co.uk