Thread: Indexes

Indexes

From

Jake Stride

Date:

02 August 2005, 11:52:44

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

Re: Indexes

From

Kilian Hagemann

Date:

02 August 2005, 12:07:33

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

Re: Indexes

From

Peter Wilson

Date:

02 August 2005, 12:29:06

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

Re: Indexes

From

Richard Huxton

Date:

02 August 2005, 12:31:02

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

Re: Indexes

From

Jake Stride

Date:

02 August 2005, 13:07:39

Peter Wilson wrote:
> 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
>>
>>
>
> 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
Does saying 'main' not mean where main=true as it is a boolean

Re: Indexes

From

Tom Lane

Date:

02 August 2005, 14:53:19

Jake Stride <nsuk@users.sourceforge.net> writes:
> Does saying 'main' not mean where main=true as it is a boolean

It means the same, but that doesn't make it an indexable condition.
In Postgres, the index machinery is built around operators; if you
don't have a WHERE clause like "indexvar operator something" then
you don't have an indexable condition.

PG 8.1 will recognize that it can convert a boolean "main" to
"main = true" and then use an index on main, but no existing release
will do so.  Here's the CVS log entry about it:

2005-03-26 18:29  tgl

    * src/: backend/optimizer/path/indxpath.c,
    backend/optimizer/path/orindxpath.c,
    backend/optimizer/util/pathnode.c, include/catalog/pg_opclass.h,
    include/optimizer/paths.h: Expand the 'special index operator'
    machinery to handle special cases for boolean indexes.    Previously
    we would only use such an index with WHERE clauses like 'indexkey =
    true' or 'indexkey = false'.  The new code transforms the cases
    'indexkey', 'NOT indexkey', 'indexkey IS TRUE', and 'indexkey IS
    FALSE' into one of these.  While this is only marginally useful in
    itself, I intend soon to change constant-expression simplification
    so that 'foo = true' and 'foo = false' are reduced to just 'foo'
    and 'NOT foo' ... which would lose the ability to use boolean
    indexes for such queries at all, if the indexscan machinery
    couldn't make the reverse transformation.

            regards, tom lane