Re: Indexes - Mailing list pgsql-general

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