Re: Indexes

On Tuesday 02 August 2005 13:52, Jake Stride pondered:
> 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?
>
Have you VACUUM ANALYZE'd the table recently?

I had a similar problem with my queries not using index scans when they
should, check out my thread at
http://archives.postgresql.org/pgsql-general/2005-07/msg00866.php

Hope this helps.

--
Kilian Hagemann

Climate Systems Analysis Group
University of Cape Town
Republic of South Africa
Tel(w): ++27 21 650 2748