Re: Indexes

Jake Stride wrote:
>
> 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?

Well, it's returning 2000 rows, so unless the table is much larger than
that (say 25000 rows or more) then a seq-scan will be faster. Don't
forget that PG will have to load a whole page at a time when it accesses
the disk, so worst case you could end up reading one page (containing
say 20 rows) for every row in your result.

In addition, an index on boolean THEN char is unlikely to be very selective.

To test, execute "SET enable_seqscan=false;" then run your explain
analyse again - that should force it to use the index.

--
Richard Huxton
Archonet Ltd