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
