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
---------------------------(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
