Re: multi-column index

Daniel,

> Table "public.descriptionprodftdiclnk"

What is this, German? ;-)

> explain analyze select * from descriptionprodftdiclnk where idword=44;
> QUERY PLAN
> ---------------------------------------------------------------------------
>---------------------------------------------------- Seq Scan on
> descriptionprodftdiclnk (cost=0.00..4788.14 rows=44388 width=8) (actual
> time=87.582..168.041 rows=43792 loops=1)
> Filter: (idword = 44)
> Total runtime: 195.339 ms
> (3 rows)

> explain analyze select * from descriptionprodftdiclnk where idword=44;
>
> QUERY PLAN
> ---------------------------------------------------------------------------
>----------------------------------------------------------------------------
>------------ Index Scan using descriptionprodftdiclnk_pkey on
> descriptionprodftdiclnk (cost=0.00..36720.39 rows=44388 width=8)
> (actual time=0.205..73.489 rows=43792 loops=1)
> Index Cond: (idword = 44)
> Total runtime: 100.564 ms
> (3 rows)

> create index ix_tempIndex on descriptionprodftdiclnk(idword);
> CREATE INDEX
> explain analyze select * from descriptionprodftdiclnk where idword=44;
> QUERY
> PLAN
> ---------------------------------------------------------------------------
>---------------------------------------------------------------------- Index
> Scan using ix_tempindex on descriptionprodftdiclnk
> (cost=0.00..916.24 rows=44388 width=8) (actual time=0.021..79.879
> rows=43792 loops=1)
> Index Cond: (idword = 44)
> Total runtime: 107.081 ms
> (3 rows)
>
> Could someone provide an explanation for the planner's behaviour?

Pretty simple, really. Look at the cost calculations for the index scan for
the multi-column index. PostgreSQL believes that:
The cost of a seq scan is 4788.14
The cost of an 2-column index scan is 36720.39
The cost of a 1-column index scan is 916.24

Assuming that you ran each of these queries multiple times to eliminate
caching as a factor, the issue is that the cost calculations are wrong. We
give you a number of GUC variables to change that:
effective_cache_size
random_page_cost
cpu_tuple_cost
etc.

See the RUNTIME-CONFIGURATION docs for more details.

--
Josh Berkus
Aglio Database Solutions
San Francisco