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